如何更新我在其中进行SELECT查询的表？ （错误＃1093）

Question

I am trying to run this:

$stmt=$cxn->prepare("UPDATE table1 SET used='1' WHERE prim_id != 
     (SELECT MAX(prim_id) FROM table1 WHERE email='[e-mail]')");
$stmt->execute(array());

But it results in a MySQL error: #1093 - You can't specify target table 'table1' for update in FROM clause.

After searching this error, it seems that In MySQL, you can't modify the same table which you use in the SELECT part..

How can I change that query to make it work?

Thanks a lot in advance

Answer 1

Try this:

UPDATE table1 t1, (SELECT MAX(prim_id) AS max_prim_id
                     FROM table1 WHERE email='[e-mail]') tmp
SET t1.used='1'
WHERE t1.prim_id != tmp.max_prim_id
AND t1.`email` = '[e-mail]'

It removes the subquery and uses a regular temporary table to store the result of table "tmp". The query takes 2 tables for update:

• table1 (with the alias t1)
• a temporary table (with the alias tmp)

The WHERE clause:

• indicates how the two tables are joined
• restricts the records to consider in table t1

The SET clause says that only t1.used will be updated.

UPDATE statement