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 should compose it initially as a query to return the rows of interest. Once that's all debugged, then convert it into a delete.

Answer 2

The syntax is incorrect - you don't reference columns between the DELETE and FROM. Use:

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.

Answer 3

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