Search code examples
sqlsql-servert-sqlsql-delete

Delete rows from 2 tables using a single query

I have the following database-schema:

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

Solution

  • 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.