Mysql – How to remove multiple records from one table based on the non-existence of a record in another table

MySQL

I have two tables, 'Products' and 'Details'.

--------------------------
Products [ID, Name]
--------------------------
1, Link's Simple Syrup
2, Team Shirt
3, Animal DVD

-----------------------------------------------------------
Details [ID, Product_id, attribute_name, attribute_value]
-----------------------------------------------------------
1, 1, potency, "high"
2, 2, color, "blue"
3, 2, size, "large"
4, 2, words, "go team"
5, 3, title, "Animals with wide angle lenses"
6, 3, price, "1 million dollars"

So say 'Team Shirt' gets removed from the Products table, and now I need to clean up (remove) the 3 leftover Details it used.
Basically I want to look at each record from the Detail table and see if it's product_id corresponds to an existing ID in the products table.
If I can construct a SELECT statement that returns only these orphaned Detail records, then I can change select to DELETE and remove them.
That's my thinking anyway, any help creating the SQL query to accomplish that or suggestions on a different approach altogether if necessary would be much appreciated.

pseudocode of the logic might be something like this:

For each record in Details{
    if post_id is a NULL ID in Products delete this record
}

Best Answer

Two ways to accomplish that:

DELETE FROM details WHERE product_id NOT IN (SELECT id FROM products)

However, it is recommended to use left join:

DELETE details FROM details LEFT JOIN products ON details.product_id=products.id WHERE products.id IS NULL

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.

Best Answer

Related Solutions

MySQL – How to Update One Table Based on Another Table’s Values

Mysql – Update one table from another table while sorting that table based on one column