how to fix the SQL Error [1093] and Error [1064]?-CodePudding

I am getting this error

SQL Error [1093] [HY000]: You can't specify target table 'id_card' for update in FROM clause

for my this query

update id_card set id_card_status= 'ACTIVE' where code in (select code from ID_CARD ic
left join PROJECT p on p.PROJECT_code=ic.project   and ic.VERSION =p.current_id_card_version 
where ic.project_type in (670 ,671 ,672 ,673) and ic.id_card_status ='DRAFT')

After searching here I rewrite the query like this

update id_card as table_1, (select code from ID_CARD ic
left join PROJECT p on p.PROJECT_code=ic.project   and ic.VERSION =p.current_id_card_version 
 ) as table_2  
where table_1.code = table_2.code 
and table_1.project_type in (670 ,671 ,672 ,673) 
and table_1.id_card_status ='DRAFT'
Set table_1.id_card_status = 'ACTIVE'

But I got another error

 SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL 
    server version for the right syntax to use near 'where table_1.code = table_2.code 
        and table_1.project_type in (670 ,671 ,672 ,' at line 4

How can I re-write this ?

CodePudding user response：

Check the syntax for multi-table UPDATE in MySQL. This syntax is non-standard, so different vendors implement it differently.

https://dev.mysql.com/doc/refman/8.0/en/update.html says:

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

So the SET clause goes before the WHERE clause.

It's not an error, but I encourage you to use ANSI SQL 92 JOIN syntax consistently, not comma-join syntax. It will help you avoid some weird surprises with respect to operator precedence.