CODEWITHSUNDEEP
mysqlsql
Captain Ahab
Captain Ahab

Reputation: 422

MySQL: delete rows with "WHERE ... NOT IN" from only one single table

In a MySQL database I have a many-to-many relationship between two tables. For the sake of simplicity let's assume those tables map homes and their residents. I have a third table to map those relations (home_resident_relations). The latter table has an additional column datemodified that stores the date of the latest update of each row via triggers.

Now I want to get rid of all former residents for each home and only keep the current ones - that is those with the newest date. I have already a working SELECT clause that will list me all old relations I want to delete:

SELECT * FROM `home_resident_relations` WHERE `resident_id` NOT IN 
    (SELECT tbl.`resident_id`   
    FROM `home_resident_relations` tbl
    WHERE tbl.`datemodified` = 
        (SELECT max(tbl2.`datemodified`) 
        FROM `home_resident_relations` tbl2 
        WHERE tbl2.`home` = tbl.`home` 
        GROUP BY tbl2.`home`)
    OR tbl.`datemodified` IS NULL
);

Now it would be a straight-forward idea to simply replace the SELECT * with a DELETE command to remove all those rows. However, this does not work due to error

#1093 - You can't specify target table 'home_resident_relations' for update in FROM clause

So here's my question:

How do I delete from a table while using it in the WHERE ... NOT IN clause?

Upvotes: 1

Views: 1569

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Use a left join instead:

DELETE hrr
    FROM `home_resident_relations` hrr LEFT JOIN
         (SELECT tbl.`resident_id`   
          FROM `home_resident_relations` tbl
          WHERE tbl.`datemodified` = (SELECT max(tbl2.`datemodified`) 
                                      FROM `home_resident_relations` tbl2 
                                      WHERE tbl2.`home` = tbl.`home` 
                                      GROUP BY tbl2.`home`
                                     ) OR
               tbl.`datemodified` IS NULL
         ) tt
         ON hrd.resident_id = tt.resident_id
    WHERE tt.resident_id IS NULL;

This works for both the SELECT and DELETE.

Upvotes: 1

sagi
sagi

Reputation: 40491

Try using DELETE with join:

DELETE FROM `home_resident_relations`
LEFT OUTER JOIN 
               (SELECT tbl.`resident_id`   
                FROM `home_resident_relations` tbl
                WHERE tbl.`datemodified` = 
                                          (SELECT max(tbl2.`datemodified`) 
                                           FROM `home_resident_relations` tbl2 
                                            WHERE tbl2.`home` = tbl.`home` )
                OR tbl.`datemodified` IS NULL) s
 ON(s.`resident_id` = `home_resident_relations`.`resident_id`)
WHERE s.`resident_id` is null

Upvotes: 1

Related Questions