Stack Overflow
Stack Overflow

Questions tagged [mysql-error-1093]

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

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

Explanation

The MySQL UPDATE statement doesn't allow subqueries against the same table that is being updated. IE: The following will fail with the 1093 error message:

UPDATE TABLE_A
 WHERE col IN (SELECT col2
                 FROM TABLE_A)

...or:

UPDATE TABLE_A
   SET ...
 WHERE col = (SELECT col2
                 FROM TABLE_A)

Solutions

There are two means of solving this issue:

  • MySQL specific - Use the ANSI-92 JOIN syntax:

    UPDATE TABLE_A a
  JOIN TABLE_A b ON b.col2 = a.col
   SET ...

  • Bury the subquery one level deeper

    UPDATE TABLE_A
   SET ...
 WHERE col = (SELECT x.col 
                FROM (SELECT col2 AS col
                        FROM TABLE_A) AS x)
84 questions
0
votes
2 answers

Mysql error:1093 - Can't specify target table for update in FROM clause

I have a table employees in my database with corrupt entries. I tried to delete them excuting: delete from employees where id_boss= ( select id_worker from employees e where surname= 'XXX') AND basic_wage>1500 but I get the next error: #1093 -…
Balgore
  • 11
  • 1
  • 3
0
votes
1 answer

mysql max strict mode

I have the following query: DELETE FROM table1 WHERE node_id = ".$id." AND date < ( SELECT (MAX(date)- INTERVAL 1 MONTH) from table1 WHERE node_id = ".$id." ) However due to mysql strict mode…
Charabon
  • 657
  • 1
  • 9
  • 21
0
votes
0 answers

Error: Update with select in MySQL 5

Next everybody, I'm trying to make a query (UPDATE) to debit credits in XX (value) of a company identified by script_id + cnpj. Ie, the value will dynamically and identification of the company as well.   When I run the following error appears: [Err]…
mtavaresc
  • 11
0
votes
1 answer

annual change of values with subquery

This query gives a 1093 error: UPDATE values_table as outer_select SET annual_change = sample_value - ( SELECT sample_value FROM values_table WHERE date_sampled = DATE_SUB(outer_select.date_sampled, INTERVAL 1 YEAR) ); I'm…
user1382306
0
votes
1 answer

Error 1093. You can't specify target table 'ps_order_history' for update in FROM clause

I have been searching, but I still don't understand why it's wrong. I checked the subqueries, and they're returning just 1 row. UPDATE ps_order_history SET id_order_state = 18 WHERE id_order = ( SELECT max(p.id_order) FROM ps_orders p …
Keoma Borges
  • 668
  • 2
  • 10
  • 27
0
votes
1 answer

correlated subquery update

I have decided in my table that I no longer want to record start and finish times, but rather just a start time and a duration in minutes. How can I update my table so that my new column has its values inserted based on the existing data? my…
Robert
  • 7,768
  • 8
  • 33
  • 56
0
votes
4 answers

Update with SELECT and group without GROUP BY

I have a table like this (MySQL 5.0.x, MyISAM): response{id, title, status, ...} (status: 1 new, 3 multi) I would like to update the status from new (status=1) to multi (status=3) of all the responses if at least 20 have the same title. I have this…
Toto
  • 2,142
  • 20
  • 37
0
votes
1 answer

You can't specify target table XXX for update in FROM clause (with SUM in subclause)

I'm trying to execute this query: UPDATE arc_salon_credit_exposant AS asce SET asce.asce_credit_restant = ( SELECT SUM(asce_credit_restant) …
MiGU
  • 382
  • 3
  • 17
-2
votes
1 answer

#1093 - You can't specify target table 'rent' for update in FROM clause

"UPDATE rent SET remaining='$remnow' WHERE date=(select max(date) from rent where shopno='$shopno')";
Muhammad Qasim
  • 1
1 2 3 4 5
6