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?

You don't seem to be using the Transaction table you have joined with elsewhere in your query. Is this intentional? — Mark Byers, Jan 05 '10 at 20:42
Make sure you have backed up your data / database before you do a delete. — Raj More, Jan 05 '10 at 20:42
Is Transaction_Type a table? It looks like you're trying to remove 2 columns from a table rather than rows... — Nathan Wheeler, Jan 05 '10 at 20:44

score 25 · Accepted Answer · answered Jan 05 '10 at 20:41

25

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

answered Jan 05 '10 at 20:41

David M

71,481
13
158
186

You have to specify the tables to delete from, either with names or aliases, between `DELETE` and `FROM` in a multi-table delete – David M Jan 05 '10 at 20:48
@md5sum: That's for multi-table deletes, but I too don't know if it makes a diff in this example – OMG Ponies Jan 05 '10 at 20:48
@DavidM Great answer and comment, solved my problem almost 3 years later =) – Windle Dec 06 '12 at 23:34
@Windle - good, love it when an old answer still helps people out. – David M Dec 07 '12 at 08:22

score 1 · Answer 2 · answered Jan 05 '10 at 20:41

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.

Don't think that will work - check my reply to your comment above. — David M, Jan 05 '10 at 20:48

score 0 · Answer 3 · answered Nov 03 '12 at 06:18

0

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.

answered Nov 03 '12 at 06:18

Amit Gajjar

51
5

score 0 · Answer 4 · answered Jan 05 '10 at 20:41

0

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.

answered Jan 05 '10 at 20:41

wallyk

56,922
16
83
148

DELETE data from a table, joining through two tables

4 Answers4