DELETE data from a table, joining through two tables

Question

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?

Answer 1

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