CODEWITHSUNDEEP
sqlsql-servert-sql
Jamie Rees
Jamie Rees

Reputation: 8183

Deleting from multiple tables using a left join in one query

I am wondering if I can do the following, I have 3 tables that I want to delete data from : Table1, Table2, Table3

There will always be a record in Table1 and there might or might not be an associated record in Table2 and Table3.

So I thought I could do the following:

DELETE FROM dbo.Table1, dbo.Table2, dbo.Table3
FROM Table1 t1
LEFT JOIN dbo.Table2 t2 ON t1.Id = t2.Id
LEFT JOIN dbo.Table3 t3 ON t3.Id = t1.Id
WHERE t1.Id = @Id;

But I get an Incorrect syntax near ','. because of the DELETE FROM dbo.Table1, dbo.Table2, dbo.Table3 line.

The desired results is to have all the data from the 3 tables deleted with the Where clause.

I'm not sure how to acomplish this.

Upvotes: 0

Views: 1710

Answers (5)

Redkram
Redkram

Reputation: 1

**DELETE t1,t2,t3 FROM**
FROM Table1 t1
LEFT JOIN dbo.Table2 t2 ON t1.Id = t2.Id
LEFT JOIN dbo.Table3 t3 ON t3.Id = t1.Id
WHERE t1.Id = @Id;

Upvotes: 0

Thom A
Thom A

Reputation: 95574

A couple of methods to this. Personally, I would recommend the SP over the trigger; like others I'm not a huge fan of triggers as they can often be a "hidden" entity (far too many times have I been unable to work out why something isn't working to find it it was a trigger I wasn't aware of).

Anyway, there trigger method would be:

CREATE TRIGGER Cascade_Table2_Table3 ON dbo.Table1
AFTER DELETE
AS

    DELETE T2
    FROM Table2 T2
         JOIN deleted d ON t2.Id = d.Id;

    DELETE T3
    FROM Table3 T3
         JOIN deleted d ON t3.Id = d.Id;
GO

However, as you're using a single value, then a parametrised Stored Procedure would be far better; and then you DENY users from running a DELETE on the tables:

CREATE PROC Delete_T1T2T3 @ID int AS

    DELETE FROM dbo.Table1
    WHERE ID = @ID;

    DELETE FROM dbo.Table2
    WHERE ID = @ID;

    DELETE FROM dbo.Table3
    WHERE ID = @ID;

GO

Upvotes: 2

Ilyes
Ilyes

Reputation: 14928

Here is another way using OUTPUT clause

CREATE TABLE T1( ID INT);
CREATE TABLE T2( ID INT);
CREATE TABLE T3( ID INT);

INSERT INTO T1 VALUES (1), (2);
INSERT INTO T2 VALUES (1), (3);
INSERT INTO T3 VALUES (1), (4);

BEGIN TRAN;

 DECLARE @Out TABLE (IDs INT);

 DELETE T1
 OUTPUT DELETED.ID INTO @Out
 FROM T1 INNER JOIN T2 ON T1.ID = T2.ID;

 DELETE T2
 FROM T2 INNER JOIN @Out O ON T2.ID = O.IDs;

 DELETE T3
 FROM T3 INNER JOIN @Out O ON T3.ID = O.IDs;

COMMIT TRAN;

SELECT *
FROM T1;
SELECT *
FROM T2;
SELECT *
FROM T3;

Demo

Upvotes: 1

user10679624
user10679624

Reputation:

can you use a trigger on table1?, so you can delete records only from table1 and then let the trigger delete stuff from table2 and table3.

Something like:

CREATE TRIGGER [dbo].[T_deleteT2_t3]
    ON [dbo].[Table1]
    AFTER  DELETE
AS

BEGIN

    delete from table2 where id = select id from deleted
    delete from table3 where id = select id from deleted

END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can only delete from one table at a time. Presumably, you want cascading constraints:

alter table2 add constraint fk_table2_table1
    foreign key (id) references table1(id)
    on delete cascade;

alter tablee add constraint fk_tablee_table1
    foreign key (id) references table1(id)
    on delete cascade;

Foreign keys ensure data integrity. Cascading constraints mean that when the primary key is deleted, then the corresponding rows in the other tables are also removed.

Upvotes: 5

Related Questions