DELETE data from a table, joining through two tables

mysql, sql, sql-delete

Solution

You need to delete rows from `tt`, not individual columns:

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

Problem

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... Unkown table 'Transaction_Amount' in MULTI DELETE I've tried reading the mysql manual and it seems like this should work to me... any one have any idea's?

Original source