Reputation: 1784
Hi have a 2 tables organized in this way:
table_users
|user_id|username|
table_products
|product_id|user_id|
Now, I have some orphan entries in 'table_products'. With this query I check how many products I have:
SELECT count(product_id) FROM table_products
This query show me the number of the products associated to the users:
SELECT p.product_id, u.username
FROM table_products AS p
JOIN table_users AS u ON u.user_id = p.user_id
I need a query that allow me to select and remove all the orphans products. Anyone can help me ?
Upvotes: 0
Views: 73
Reputation: 332791
DELETE FROM TABLE_PRODUCTS
WHERE user_id NOT IN (SELECT u.user_id
FROM TABLE_USERS u)
DELETE FROM TABLE_PRODUCTS
WHERE NOT EXISTS (SELECT NULL
FROM TABLE_USERS u
WHERE u.user_id = TABLE_PRODUCTS.user_id)
DELETE TABLE_PRODUCTS
FROM TABLE_PRODUCTS p
LEFT JOIN TABLE_USERS u ON u.user_id = p.user_id
WHERE u.user_id IS NULL
If the TABLE_PRODUCTS.user_id
is nullable, the NOT IN and NOT EXISTS are more efficient choices. Otherwise, the LEFT JOIN is the most efficient choice in MySQL when the columns compared can not be null.
Check and double check that you are selecting the correct rows for deletion before performing the action by swapping out "DELETE FROM" for "SELECT * FROM". Additionally, if using InnoDB tables - encapsulate the DELETE in a transaction so you can use ROLLBACK
if necessary.
Upvotes: 2
Reputation: 62395
Warning: I am not taking any responsibility for data deleted with this query. Always test on test database before running on production data.
DELETE p
FROM table_products AS p
LEFT JOIN table_users AS u
USING (user_id)
WHERE u.user_id IS NULL
Upvotes: 1