Geek Answers Handbook

SQL: delete rows whose associations are broken (lost data)

I have a table called "downloads" with two columns of the foreign key - "user_id" and "item_id". I need to select all the rows from this table and delete the rows in which the user or the item in question no longer exists. (Look at the user, and if he is not found, delete the line in "downloads", then find the item, and if he is not found, delete the line in "downloads").

This is 3.4 million lines, so all my scripting solutions took 6 hours. I hope there is a faster, only SQL way?

+5
source share
5 answers

use two anti-compounds and orthem 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)

as soon as this is done, consider adding two foreign keys, each of which contains a proposal for exclusion from the cascade. he will automatically do it for you.

+25
source 
delete from your_table where user_id not in (select id from users_table) or item_id not in (select id from items_table)
+3
source

, , 157

mysql num rows = 0, , item_id

myswl num

MySQL:

edit: , , .

cronjob

0

. . SQL. , sql, , , sql , . , , .

0

SQL 2008 R2, "in" (, , , ), ! , :

delete from SomeTable where Key not in (
  select SomeTableKey from TableB where SomeTableKey is not null
  union
  select SomeTableKey from TableC where SomeTableKey is not null
)
0

More articles:


All Articles