Menu
I have a table called "downloads" with two foreign key columns -- "user_id" and "item_id". I need to select all rows from that table and remove the rows where the User or the Item in question no longer exists. (Look up the User and if it's not found, delete the row in "downloads", then look up the Item and if it's not found, delete the row in "downloads").
It's 3.4 million rows, so all my scripted solutions have been taking 6+ hours. I'm hoping there's a faster, SQL-only way to do this?
267
You can delete orphan records using the Compare script, Find_orphans. xml, and Delete script which are bundled with the Find_orphans_package attached to this page.
If you know the value of the primary key for the row that you want to delete, you can specify the value using the VIA clause. If there is more than one primary key column, the values must be specified in order and separated by commas (,). String values must be enclosed in single quotes (').
use two anti joins and or them together:
delete from your_table
where user_id not in (select id from users_table)
or item_id not in (select id from items_table)
once that's done, consider adding two foreign keys, each with an on delete cascade clause. it'll do this for you automatically.
163
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
