Mysql ERROR 1093 (HY000) updating a row

Question

I want to update a table by updating the id of a row (id is auto increment). this is the code:

 update question_bank set id=1 where id=(select min(id) from question_bank where id>=1);

but I received this error:

ERROR 1093 (HY000): You can't specify target table 'question_bank' for update in FROM clause

What should I do? Thanks :)

you should maybe not do this, or use the update with a join pattern (not that that helps with PK conflicts anyway) — Drew, Aug 23 '15 at 12:12
possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — jpw, Aug 23 '15 at 12:13
@Drew Can you comment the right form of the code. My knowledge about join pattern is not good! — Amir Havangi, Aug 23 '15 at 12:15
not touching this one with a 10 foot pole: create table xxx ( id int auto_increment, theWhat varchar(80) not null ); Error 1075. Then PK conflict issues. To me, ids don't change. Sad orphaned children result. Granted, you could jam a key(id) at end. But what good is the auto_increment without it being primary key? — Drew, Aug 23 '15 at 12:19

score 1 · Accepted Answer · answered Aug 23 '15 at 12:13

If your ids are unique, you can use this:

update question_bank qb
    set id = 1
    where id >= 1
    order by id
    limit 1;

Or, you can use a join:

update question_bank qb join
       (select min(id) as minid from question_bank where id>=1) qb2
       on ab.id = qb2.minid
    set qb.id = 1;

Or, you can use a MySQL trick to get around this problem but using another level of subqueries:

update question_bank
    set id = 1
    where id = (select minid
                from (select min(id) as minid
                      from question_bank
                      where id >= 1
                     ) qb
               );

Mysql ERROR 1093 (HY000) updating a row

1 Answers1