Database Reference
In-Depth Information
ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
You would expect this to work well, but it doesn't work at all. Instead, it fails and returns
the error message shown. When using the multiple-table syntax of
UPDATE
, it causes
problems for MySQL if youinclude an
ORDER BY
or a
LIMIT
clause — those clauses
apply to one table, not to multiple tables as in this
UPDATE
. Limitations like this can be
frustrating, but there are ways around them. For our current task, because the
ORDER BY
RAND()
and
LIMIT
clauses work with one table without problems, we can use a sub-
query (i.e., a query within a query) to randomly select the winners from the
humans
table
and then update the
prize_winners
table. Let's see how we would do that in this situ-
ation:
UPDATE
prize_winners
SET
winner_date
=
CURDATE
()
WHERE
winner_date
IS
NULL
AND
human_id
IN
(
SELECT
human_id
FROM
humans
WHERE
country_id
=
'uk'
ORDER BY
RAND
())
LIMIT
2
;
That may seem pretty complicated, but if we pull it apart, it's not too difficult. First, let's
look at the inner query, the
SELECT
statement contained within the parentheses. It's se-
lecting the
human_id
for all members in the
humans
table, where the
country_id
has a value of
uk
, and randomly ordering the results. Notice that we're selecting all rows
for U.K. members and we're not distinguishing whether the member was a previous win-
ner. That's because the inner query cannot query the table that is the target of the
UPDATE
. So we have to separate the conditions like we're doing here: in the
WHERE
clause of the
UPDATE
, we're updating only rows in which the value of the
win-
ner_date
is NULL. That will be all of the U.K. members.But we could change the
statement to select non-U.K. members simply by changing the operator in the subquery to
!=
.
In the
UPDATE
statement, using the
IN
operator, we specify that only rows whose
hu-
man_id
is in the results of the subquery should be updated. The
LIMIT
clause says to
update only two rows. The
LIMIT
clause here is part of the
UPDATE
, not the subquery
(i.e., the
SELECT
).
