I have the following database-schema:
I have the following example data:
CREATE TABLE computermapping ( ComputerMappingID int NOT NULL, PrinterGUID char(36) NOT NULL, ComputerGUID char(36) NOT NULL ); INSERT INTO computermapping (ComputerMappingID, PrinterGUID, ComputerGUID) VALUES (1, 'PRT01', 'Computer1'), (2, 'PRT02', 'Computer1'), (3, 'PRT01', 'Computer2'), (4, 'PRT02', 'Computer2'), (5, 'PRT03', 'Computer2'), (6, 'PRT01', 'Computer3'); CREATE TABLE computerdefaultprinter ( ComputerGUID char(36) NOT NULL, PrinterGUID char(36) NOT NULL ); INSERT INTO computerdefaultprinter (ComputerGUID, PrinterGUID) VALUES ('Computer2', 'PRT01'), ('Computer1', 'PRT02');
Remark: Originally the tables are full of GUIDs, but I replaced them by names just for better readability.
I have also created an SQL-Fiddle with some example-data: Link
.
Taking the example data, I want to remove the printer “PRT01” from computer “Computer2“.
I need to delete the appropriate row in the table computermapping
and I need to delete the appropriate row in the table computerdefaultprinter
. I want to delete the mentioned data in BOTH tables using ONE SINGLE statement.
According to my program-code I need to target the data by using NOT IN()
.
Till now I successfully used 2 statements joined/glued together by “;”:
DELETE FROM computermapping WHERE PrinterGUID = 'PRT01' AND ComputerGUID NOT IN ('Computer1','Computer3'); DELETE FROM computerdefaultprinter WHERE PrinterGUID = 'PRT01' AND ComputerGUID NOT IN ('Computer1','Computer3')
This was working fine using MySQL, but it is not working with Microsoft SQL-Server. Yes, it does using the SQL Server Management Studio, but not programmatically. (count field incorrect or syntax error)
I am looking for a different approach for this task. I did a research and it was mentioned, that it should be possible to delete the rows in both tables using “INNER JOIN”, but I wasn’t able to get it working and I am looking for help.
Thank you
Advertisement
Answer
You can add a foreign key with ON DELETE CASCADE
.
For example:
CREATE TABLE computermapping ( ComputerMappingID int NOT NULL, PrinterGUID char(36) NOT NULL, ComputerGUID char(36) NOT NULL, primary key (ComputerGUID, PrinterGUID) ); INSERT INTO computermapping (ComputerMappingID, PrinterGUID, ComputerGUID) VALUES (1, 'PRT01', 'Computer1'), (2, 'PRT02', 'Computer1'), (3, 'PRT01', 'Computer2'), (4, 'PRT02', 'Computer2'), (5, 'PRT03', 'Computer2'), (6, 'PRT01', 'Computer3'); CREATE TABLE computerdefaultprinter ( ComputerGUID char(36) NOT NULL, PrinterGUID char(36) NOT NULL, foreign key (ComputerGUID, PrinterGUID) references computermapping (ComputerGUID, PrinterGUID) on delete cascade ); INSERT INTO computerdefaultprinter (ComputerGUID, PrinterGUID) VALUES ('Computer2', 'PRT01'), ('Computer1', 'PRT02'); delete from computermapping where PrinterGUID = 'PRT01' and ComputerGUID = 'Computer2';
The DELETE
deletes a row in computermapping
and all related rows from computerdefaultprinter
as well.
See running example at SQL Fiddle.