CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3003821
user3003821

Reputation:

single query to delete from multiple tables in sql server

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?

Upvotes: 1

Views: 12399

Answers (2)

sarathkumar
sarathkumar

Reputation: 428

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

  1. Create ForeignKey with cascade delete enabled option
  2. 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

Upvotes: 0

PrfctByDsgn
PrfctByDsgn

Reputation: 1050

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

Upvotes: 2

Related Questions