Argthtjtr

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.

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.

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

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

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