Menu
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?
860
DELETE JOIN is an advanced structured query language(SQL) statement that is used to perform delete operations in multiple tables while using SQL JOIN such that all rows are deleted from the first table and the matching rows in another table or based on the kind of join operation used in the query.
A DELETE statement can include JOIN operations. It can contain zero, one, or multiple JOIN operations. The DELETE removes records that satisfy the JOIN conditions.
To join two tables based on a column match without loosing any of the data from the left table, you would use a LEFT OUTER JOIN. Left outer joins are used when you want to get all the values from one table but only the records that match the left table from the right table.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
