Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello,

How to delete a row from 2 tables with single query.

For eg., I have 2 tables named tbl1, tbl2.

Under tbl1..

tbl1
__________________
username | address
------------------
shiva    | US
samuel   | UK



under tbl2..
tbl2
__________________
username | location
-------------------
joseph   |  Canada
shiva    |  Hongkong



Above are my table structures and their respective data..

What my problem is How can I delete a record with username = 'shiva' from both tables simultaneously using single query..


Thanks..

I have tried using joins like..

SQL
DELETE tbl1, tbl2 FROM tbl1 INNER JOIN tbl2 ON tbl1.username = tbl2.username WHERE tbl1.username='shiva' 


but i am getting error i.e., incorrect syntax near ,


[edit]Code block added[/edit]
Posted
Updated 25-Jun-13 3:41am
v3

You can only do it if you have a constraint with cascading delete between the two tables.

This link can help you with cascading delete, I think[^]

Another way of doing it is to do it in two queries wrapped in a transaction.

Information on transactions[^]
 
Share this answer
 
Comments
Uday P.Singh 25-Jun-13 9:24am    
agree 5!
Bhargav.456 25-Jun-13 9:30am    
Hi.. Thanks for the reply..
I have tried in this way..
DELETE tbl1, tbl2 FROM tbl1 INNER JOIN tbl2 ON tbl1.username = tbl2.username WHERE tbl1.username='shiva'

but i am getting error i.e., incorrect syntax near ,
Johnny J. 25-Jun-13 9:33am    
Yeah, well, you can't do it like that... Try to look at the links instead. It's not possible to write a query that can delete from two tables at once. You need to use one of the other techniques, either a constraint, a transaction or a trigger as Tiwari mentions (even though I think using a trigger for this is a too complicated solution)

Take my advice: Every minute you work to try to find a "one query solution" will be wasted. Abandon it directly and try to apply one of the other solutions!
Bhargav.456 25-Jun-13 9:36am    
Thanks
Johnny J. 25-Jun-13 9:39am    
A solution OTHER than the correct ones??? What would that be???
you can use a trigger on delete

http://blog.sqlauthority.com/category/sql-trigger/[^]
 
Share this answer
 
v2
You can use it...


SQL
begin transaction;

   declare @deletedIds table ( id int );

   delete t1
   output deleted.id into @deletedIds
   from table1 t1
    join table2 t2
      on t2.id = t1.id
    join table3 t3
      on t3.id = t2.id;

   delete t2
   from table2 t2
    join @deletedIds d
      on d.id = t2.id;

   delete t3
   from table3 t3 ...

commit transaction;



or....


SQL
DELETE FROM TB1 WHERE columnName=@variable
DELETE FROM TB2 WHERE  columnName=@variable 
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900