Condition of a query with a subquery

Question

Navigation

#1 by (3 votes)

3

I need to update the last 110 records of the relevo table, however my problem is in the subquery in WHERE .

I'm doing it this way:

UPDATE relevo
SET id_projeto = 157
WHERE id_relevo = (SELECT id_relevo FROM relevo 
ORDER BY id_relevo DESC
LIMIT 110)

The following error is being returned:

You can not specify target table 'relief' for update in FROM clause

I also tried this way:

UPDATE relevo
SET id_projeto = ?
WHERE id_relevo IN (SELECT id_relevo FROM relevo 
ORDER BY id_relevo DESC
LIMIT 110)

The error returned:

This version of MySQL does not support 'LIMIT & IN / ALL / ANY / SOME subquery

mysql sql sql-update

asked by anonymous 09.09.2018 / 00:50

1 answer

FlexBox + CSS GRID Difference between "git add --all", "git add." and "git add - u"

score 3 · Accepted Answer

This business of updating the last records does not work very well, it is fragile, it can give the expected result once and not in another.

But if you insist on doing it simply, do not use SELECT :

UPDATE relevo
    SET id_projeto = 157
    ORDER BY id_relevo DESC
    LIMIT 110

I put it in GitHub for future reference.