CODEWITHSUNDEEP
phpmysql
alexx0186
alexx0186

Reputation: 1557

How can I update a table in which I make a SELECT query? (Error #1093)

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='[email protected]')");
$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

Upvotes: 0

Views: 78

Answers (2)

Jocelyn
Jocelyn

Reputation: 11393

Try this:

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

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

Upvotes: 1

hjpotter92
hjpotter92

Reputation: 80629

Your query is just missing some logic. Change it to:

UPDATE table1
SET used='1'
WHERE `email` = '[email protected]'
  AND prim_id != (
    SELECT MAX(prim_id) 
    FROM table1
    WHERE email='[email protected]')

Upvotes: 1

Related Questions