Delete in SQL using derived table

Question

I have a table named P, which has two columns: id and email. All the id are different and in ascending order, but some id have the same email. I need to delete all the duplicated emails and keep only unique emails with its smallest id. The correct query is:

DELETE FROM P
WHERE id NOT IN
(
 SELECT minId FROM
 (
  SELECT MIN(id) AS minId, email
  FROM P 
  GROUP BY email
 ) AS Q
)

However, it I use the following query, which returns "SyntaxError: near 'AS Q'". Could anybody tell me why. Thanks.

DELETE FROM P
WHERE id NOT IN
  (SELECT MIN(id) 
   FROM P 
   GROUP BY email
 ) AS Q

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "_SQL_" is just a query language, not the name of a specific database product. — , Oct 25 '17 at 20:16

score 0 · Answer 1 · answered Oct 25 '17 at 20:14

0

remove AS Q not in query is no longer a derived table, this is now a subquery.

DELETE FROM P
WHERE id NOT IN
(  SELECT MIN(id) 
   FROM P 
   GROUP BY email
)

answered Oct 25 '17 at 20:14

RoMEoMusTDiE

4,739
1
17
26

Thanks for the reply, I removed AS Q and then got another error: "You can't specify target table 'P' for update in FROM clause". Why don't I have the problem in the first query? – J. Shawn Oct 25 '17 at 20:18
are you running the first query or the second query? which is the official one? – RoMEoMusTDiE Oct 25 '17 at 20:25
the first query won't have a problem because you just aliased the derived table in the subquery but not the subquery... – RoMEoMusTDiE Oct 25 '17 at 20:26
Thanks, now I understand why my original second query is incorrect. What's confusing me now is that after removing the AS Q from the second query, I got an error "You can't specify target table P for updating in FROM clause". As I am also updating P in the FROM clause of my first query, why it does not return an error? – J. Shawn Oct 25 '17 at 20:37
where is your update statement? you delete statement should be okay – RoMEoMusTDiE Oct 25 '17 at 20:40
Finally found that the problem is mySql, a similar post is here: https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause – J. Shawn Oct 25 '17 at 21:03

score 0 · Answer 2 · answered Oct 25 '17 at 20:15

You would get the same error if you added AS P at the end of the last ) brace in the first query.

That said you would probably get better performance on the match if you used either a JOIN or a NOT EXISTS instead of IN

DELETE FROM P
WHERE NOT EXISTS 
(  SELECT 1
   FROM P AS p2
   GROUP BY p2.email
   HAVING MIN(p2.id) = P.id
)

score 0 · Answer 3 · answered Oct 25 '17 at 20:15

0

You should not use an alias for not in Operator:

DELETE FROM P
WHERE id NOT IN
(  SELECT MIN(id) 
   FROM P 
   GROUP BY email
)

answered Oct 25 '17 at 20:15

MelgoV

661
8
21

Delete in SQL using derived table

3 Answers3