Delete Rows using Inner Join

Question

I want to delete data from 4 table using single query.

delete e1,e2,e3,e4 FROM sas.RolesInMenuOperations as e1 INNER JOIN
sas.RolesMenus as e2 ON e1.RoleMenuId = e2.Id INNER JOIN
sas.RolesInModules as e3 ON e2.RolesInModulesId = e3.Id INNER JOIN
sas.Roles as e4 ON e3.RoleId = e4.Id
where e4.Id=5

This is what I have done so far. I was refering this link Is it possible to delete data from 4 table using Joins. How to achieve this or I want to write 4 different queries?

Though this query works properly

delete e1 FROM sas.RolesInMenuOperations as e1 INNER JOIN
sas.RolesMenus as e2 ON e1.RoleMenuId = e2.Id INNER JOIN
sas.RolesInModules as e3 ON e2.RolesInModulesId = e3.Id INNER JOIN
sas.Roles as e4 ON e3.RoleId = e4.Id
where Roles.Id=5

@JW: Deleting from multiple tables in SQL Server would require multiple queries then, right? — Rachcha
– Rachcha, Commented Mar 25, 2013 at 7:25

commit · Accepted Answer · 2013-03-25 07:53:15Z

2

You can not delete multiple table data using a single query in SQL Server. Best concept is to give FK in relative table and use ON DELETE CASCADE or use individual queries to delete from multiple tables.

answered Mar 25, 2013 at 7:53

commit

4,82715 gold badges45 silver badges71 bronze badges

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

Comments

YvesR · Accepted Answer · 2013-03-25 07:26:43Z

2

So you are looking for MS SQL syntax ? Something liek this works:

DELETE table 
FROM table a
INNER JOIN table_b b on b.id = a.id
WHERE [my filter condition]

So if you want to delete more tables rows you need to create more statements and fire them I think.

answered Mar 25, 2013 at 7:26

YvesR

6,2829 gold badges46 silver badges75 bronze badges

Collectives™ on Stack Overflow

Delete Rows using Inner Join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related