DELETE data from two tables, based on a third's ID

Question

I'm hesitant to run this query which deletes data (for obvious reasons).

I would like to delete the the matching rows from two tables, where the ID = the ID of a third table (which i want to remain unaffected)

This is the exact query I would like to run:

DELETE FROM ItemTracker_dbo.Transaction t, 
            ItemTracker_dbo.Purchase p
USING ItemTracker_dbo.Transaction
INNER JOIN ItemTracer_dbo.Purchase ON p.Transaction_ID = t.Transaction_ID
INNER JOIN ItemTracker_dbo.Item i ON i.Item_ID = p.Item_ID
WHERE i.Client_ID = 1

To test this, i tried running a select replacing DELETE FROM with SELECT * FROM, and I get a syntax error 'near USING'. When I remove USING ... it selects EVERY row in the table (ignoring the client_id=1 clause).

I (essentially) copied the syntax from the mysql manual (obviously replacing the values).

Is this query valid?

Answer 1

I'm sorry I didn't comment instead of post, but I just don't have enough reputation. Why don't you run it as a transaction and see the results? Or am I missing something? http://dev.mysql.com/doc/refman/5.0/en/commit.html

Answer 2

As far as I know, you can't do that. The way to acomplish that is with a transaction.

START TRANSACTION;

DELETE FROM tableOne WHERE criteria;

DELETE FROM tableTwo WHERE criteria;

COMMIT;

If something goes wrong between the statements, you can issue a ROLLBACK and your data will be there.

The catch is, THIS WON'T WORK WITH MyISAM. MyISAM doesn't support transactions, so it will simply delete the data and there is no way to roll it back. They need to be InnoDB tables. It does this silently. I've lost a production table that way (good thing I had a backup and it wasn't updated much).

Easiest way to find out if the tables are InnoDB?

SHOW CREATE TABLE tableName;

At the end, it will have something like 'Engine = InnoDB' or 'Engine = MyISAM'.

Answer 3

I'm not familiar with this (non-standard?) use of USING to join tables in the above, but if you just want to delete rows from only some of the tables in the join the normal way would be:

DELETE Purchase, `Transaction`
FROM Item
JOIN Purchase ON Purchase.Item_ID=Item.Item_ID
JOIN `Transaction` ON `Transaction`.Transaction_ID=Purchase.Transaction_ID
WHERE Item.Client_ID=1;

Assuming I understand your schema right. It seems a bit unusual to be deleting the Transaction that is referenced from the Purchase as this would naturally be a many-Purchase​s-to-one-Transaction relation. Is there a UNIQUE constraint to ensure it is one-to-one? If not, can you be sure no other Purchase will be referencing the deleted Transaction?

You can test the above construct as a SELECT simply:

SELECT Purchase.Purchase_ID, `Transaction`.Transaction_ID
FROM Item ...