Update from select from same table

Databases
1

This query successfully selects a unique row from a column of duplicate data:

SELECT id, info
FROM dupes
GROUP BY info
ORDER BY info

I need to update the records I just selected in the same query using something similar to this:

UPDATE dupes
SET flag = 1
WHERE
SELECT id, info
FROM dupes
GROUP BY info
ORDER BY info

How should do write that query successfully?

2

Ugh. I hate that mySQL has bastardized the GROUP BY behavior. It’s always much easier to understand when you have to select all fields and possibly have some other sort of resulting field (MAX, MIN, SUM, AVG, etc).

But to answer your question, EXISTS should give you the behavior you’re looking for:

http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

3

UPDATE dupes
SET selctd = 1
WHERE EXISTS
(SELECT id, info
FROM dupes
GROUP BY info
ORDER BY info)

Produces this error:You can’t specify target table ‘dupes’ for update in FROM clause

Interesting the the ref uses from.

Here’s my sample table called dupes:
id info selctd
1 a
2 b
3 b
4 c
5 c
6 c
7 d
8 d
9 d
10 d

what do you suggest?