MySQL 从表中删除数据，通过两个表连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2008916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DELETE data from a table, joining through two tables
提问by Derek Adair
I'm working with some rather sensitive data, so I want to be ABSOLUTELY sure I am doing it properly.
我正在处理一些相当敏感的数据，所以我想绝对确定我做得对。
I am trying to delete the rows in a table that are associated with another table
我正在尝试删除与另一个表关联的表中的行
The only way to associate the table is to join through two other tables...
关联表的唯一方法是通过另外两个表连接...
here is the exact query:
这是确切的查询：
DELETE tt.Transaction_Amount, tt.Transaction_ID
FROM ItemTracker_dbo.Transaction_Type tt
JOIN ItemTracker_dbo.Transaction t ON tt.Transaction_ID = t.Transaction_ID
JOIN ItemTracker_dbo.Purchase p ON p.Transaction_ID = tt.Transaction_ID
JOIN ItemTracker_dbo.Item i ON i.Item_ID = p.Item_ID
WHERE i.Client_ID = 1
As you can see, it ain't pretty.
正如你所看到的，它并不漂亮。
I'm getting an odd error though through the MySQL query browser...
尽管通过 MySQL 查询浏览器，我收到了一个奇怪的错误...
Unkown table 'Transaction_Amount' in MULTI DELETE
MULTI DELETE 中的未知表“Transaction_Amount”
I've tried reading the mysql manual and it seems like this should work to me... any one have any idea's?
我试过阅读 mysql 手册，似乎这对我有用……有人知道吗？
回答by David M
You need to delete rows from tt, not individual columns:
您需要从 中删除行tt，而不是单个列：
DELETE tt
FROM ItemTracker_dbo.Transaction_Type tt
JOIN ItemTracker_dbo.Transaction t ON tt.Transaction_ID = t.Transaction_ID
JOIN ItemTracker_dbo.Purchase p ON p.Transaction_ID = tt.Transaction_ID
JOIN ItemTracker_dbo.Item i ON i.Item_ID = p.Item_ID
WHERE i.Client_ID = 1
回答by OMG Ponies
The syntax is incorrect - you don't reference columnsbetween the DELETEand FROM. Use:
语法不正确 - 您没有在DELETE和之间引用列FROM。用：
DELETE FROM ItemTracker_dbo.Transaction_Type tt
JOIN ItemTracker_dbo.Transaction t ON tt.Transaction_ID = t.Transaction_ID
JOIN ItemTracker_dbo.Purchase p ON p.Transaction_ID = tt.Transaction_ID
JOIN ItemTracker_dbo.Item i ON i.Item_ID = p.Item_ID
WHERE i.Client_ID = 1
To be sure you're deleting the correct stuff, I agree with wallyk that you should check the output of the SELECT statement that what is returned is what you want to remove prior to. Otherwise, perform the delete in a transaction so you can roll it back if needed.
为确保您删除的是正确的内容，我同意 wallyk 的观点，您应该检查 SELECT 语句的输出，确定返回的内容是您之前要删除的内容。否则，在事务中执行删除，以便您可以在需要时回滚它。
回答by wallyk
You should compose it initially as a query to return the rows of interest. Once that's all debugged, then convert it into a delete.
您应该最初将其编写为一个查询以返回感兴趣的行。全部调试完成后，将其转换为删除。
回答by Amit Gajjar
Check with select query and then before executing delete query on critical data get a backup of that table. so simple as if anything going wrong then you can at least backup.
检查选择查询，然后在对关键数据执行删除查询之前获取该表的备份。如此简单，好像出了什么问题，那么您至少可以备份。
