single query to delete from multiple tables in sql server

Question

I want to delete rows from two tables using single delete query for .net application.

CREATE TABLE Table1 (User_Id, Name, Address, Group);
CREATE TABLE Table2 (User_Id, Role, Application);

INSERT INTO Table1 VALUES ('Mike', 'Michael', 'NJ', 'Dev');
INSERT INTO Table1 VALUES ('Cla', 'Clark', 'Tampa', 'Supp');
INSERT INTO Table1 VALUES ('Ton', 'Tony', 'Tulsa', 'Tes');

INSERT INTO Table2 VALUES ('Ton', 'AM', 'Science');
INSERT INTO Table2 VALUES ('Cla', 'SM', 'Magazine');
INSERT INTO Table2 VALUES ('Mike','M', 'Sports');

DELETE Table1, Table2
FROM   Table1
JOIN   Table2 ON (Table2.User_Id = Table1.User_Id)
WHERE  Table1.User_Id = '';

Pls advice whether it is a good practice or is it better to go for SP?

stackoverflow.com/questions/11825648/… Please see above post, it will answer your query. — Sach
– Sach, Commented Dec 6, 2013 at 6:22
The link goes to a question tagged mysql. Does this work on SQL Server too? — James A Mohler
– James A Mohler, Commented Dec 6, 2013 at 6:26
Delete T1, T2 from Table1 T1 inner join Table 2 T2 ON T1.User_Id = T2.User_Id where T1.User_Id = //what should I put here since I am gonna use this for asp.net application. Certainly I can put values if I am gonna run it from SQL Server — user3003821
– user3003821, Commented Dec 6, 2013 at 6:29

PrfctByDsgn · Accepted Answer · 2013-12-06 06:45:46Z

2

this is only possible if you have a foreign key constraint between your tables and activate the "cascade delete" option on the constraint.

if you don't want a constraint (although I think it would be a good idea anyway) you could use a trigger to delete corresponding records in child tables

answered Dec 6, 2013 at 6:45

PrfctByDsgn

1,0621 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

r.net Over a year ago

+1 good answer... if you don't want to / or can't change the data structure or add a trigger you can always do the two delete statements in a transaction.

sarathkumar · Accepted Answer · 2014-01-20 09:27:47Z

0

It doesn't possible directly. Of course we can. Lets do it another way

Create ForeignKey with cascade delete enabled option
Creating delete trigger on Table1

But in the case of performance i cant prefer u trigger. So enabling cascading Delete should be good.

ALTER TABLE Table2
ADD CONSTRAINT fk_Table1_User_ID
FOREIGN KEY (User_ID)
REFERENCES  Table1(User_ID)
ON DELETE CASCADE;

Cheers, Sarath

edited Jan 20, 2014 at 9:27

answered Jan 20, 2014 at 9:21

sarathkumar

4283 silver badges13 bronze badges

Collectives™ on Stack Overflow

single query to delete from multiple tables in sql server

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related