从2个表中删除一行 [英] Delete a row from 2 tables

问题描述

您好，



如何通过单个查询从2个表中删除一行。



例如，我有2个名为tbl1，tbl2的表。



在tbl1下..



tbl1

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

下的b $ b ..

tbl2

under tbl2..
tbl2

__________________
username | location
-------------------
joseph   |  Canada
shiva    |  Hongkong

以上是我的餐桌结构及其各自的数据..



我的问题是什么如何使用单个查询同时从两个表中删除带有username ='shiva'的记录..





谢谢..



我尝试过使用像...这样的联接。

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..

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

但我收到错误，即附近的语法不正确，





[edit]已添加代码块[/ edit]

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


[edit]Code block added[/edit]

推荐答案

只有在两个表之间存在级联删除约束时才能执行此操作。



此链接可以帮助您进行级联删除，我认为 [ ^ ]



另一种方法是在包含的两个查询中执行此操作交易。



交易信息 [ ^ ]

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[^]

你可以在删除时使用触发器



http://blog.sqlauthority.com/category/sql-trigger/ [ ^ ]

you can use a trigger on delete

http://blog.sqlauthority.com/category/sql-trigger/[^]

你可以使用它......

< br $> b $ b

You can use it...

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....

DELETE FROM TB1 WHERE columnName=@variable
DELETE FROM TB2 WHERE  columnName=@variable 

这篇关于从2个表中删除一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！