DELETE rows using SELECT results

By | 21 November, 2012

 

ok, so i made this nice query to..well, query two tables to check for missing ids on one of them (but it could be mostly anything else). Then i wanted to delete all the ids that dont have a match. This is kind of easily done by deleting the results from a query, but with attention to a small detail: you cant delete from a table that you are subquerying from. This is solved with a hack

So read on.

 

My nice query:

SELECT
r.id_email id_email
FROM
rel_emails_grupos r
LEFT JOIN emails e
on e.id_email=r.id_email
WHERE e.id_email IS NULL

(this query checks table “rel_emails_grupos” for id’s that dont exist in table “emails” and lists them)

Now i want to delete all those rows in “rel_emails_grupos” that resulted from the previous query:

DELETE FROM rel_emails_grupos WHERE id_email IN
(SELECT *FROM
(SELECT
r.id_email id_email
FROM
rel_emails_grupos r
LEFT JOIN emails e
on e.id_email=r.id_email
where e.id_email IS NULL) as t)

Notice that we need to add a subquery just to alias it (“AS t”) so it doesnt generate the error:

[Err] 1093 – You can’t specify target table ‘rel_emails_grupos’ for update in FROM clause

 

Stay safe out there.

 

More to read:

Stack Overflow

My second post about this

 

 

One thought on “DELETE rows using SELECT results

  1. Pingback: DELETE rows using SELECT results – part 2 @ Manifesto42 – Arquiva-me

Comments are closed.