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
1
vote
4 answers

MySQL | You can't specify target table 'a' for update in FROM clause

DELETE FROM table_a WHERE id IN( SELECT table_a.id AS id FROM table_a, table_b WHERE table_a.object_id = 1 AND table_a.code = 'code' AND table_a.code = table_b.code AND table_b.id = table_a.b_id AND table_b.table…
Simon
  • 4,449
  • 6
  • 40
  • 79
1
vote
2 answers

Can't specify target table for update, delete query in mysql

I want to do what seems like a simple delete in mysql but am having some trouble. I want to do this: delete from table where col_name not in (select distinct col_name from table); This table does not have a unique key. When I try this, I get the…
jeffery_the_wind
  • 13,565
  • 31
  • 87
  • 146
1
vote
1 answer

MySQL DELETE query issue with Select

The following query: DELETE FROM CO05IN.ININVPRC WHERE IPPART IN (SELECT IPPART FROM CO05IN.ININVPRC left join CO05IN.ININVMST on IPPART = IMPART where IMPART is null); Creates this on the log: You can't specify target table 'ININVPRC' for update…
Quaternion
  • 10,302
  • 6
  • 45
  • 98
1
vote
3 answers

You can't specify target table 'A' for update in FROM clause

This is my request > DELETE FROM A WHERE id in ( Select > id From A Left Join B on A.id=B.id > Where B.id is null ) When I execute this I have this error You can't specify target table 'A' for update in FROM clause
Mercer
  • 9,298
  • 27
  • 91
  • 154
1
vote
1 answer

SQL : Using the target table in an UPDATE statement in a nested FROM clause

I have a (mysql) database table with the following columns: NAME | String (Unique) STATUS | int UPDATE_COUNT | int (Unique) I want the value of Max(UPDATE_COUNT) to reflect the cumulative number of updates performed on rows in the table. For…
Joel
  • 27,478
  • 33
  • 104
  • 136
1
vote
3 answers

mysql error code 1093: You can't specify target table for update in FROM clause

I may be doing something wrong, I searched for it before and I find some workarounds that tell me that it can't be possible on MySQL, others are posting that it's due to MySQL optimizer so you can simply turn it off and continue, but it's not…
Joel Bonet Rodríguez
  • 113
  • 1
  • 13
1
vote
1 answer

hibernate, mysql

i have the following hql query: UPDATE TaskAssessment taskAssessment SET taskAssessment.activeFlag = false WHERE taskAssessment IN ( SELECT taskAssessment2 FROM TaskAssessment taskAssessment2 Where taskAssessment2.activeFlag = true AND…
Ikthiander
  • 3,737
  • 8
  • 33
  • 54
1
vote
1 answer

Getting around MySql Error 1093 with a NOT IN Subquery

How can I accomplish what I'm trying to accomplish with the following query? DELETE SomeDB.outertable FROM SomeDB.logging AS outertable WHERE outertable.log_id NOT IN ( SELECT SomeDB.f.log_id FROM ( SELECT aa.*,…
tex
  • 2,666
  • 20
  • 31
1
vote
1 answer

MySQL: delete from IN

I know this is a simple syntax issue. Trying to delete all users from a subquery: delete from users where id IN ( select u.id from users u where not exists (select * from stickies i where i.user_id = u.id) group by u.email having count(*) >…
jmccartie
  • 4,696
  • 8
  • 43
  • 70
1
vote
1 answer

Update record with the biggest id value where nick = 'a'

What is the fastest way to update the record with the biggest id value where nick='a' (in one query)? My table looks like this: +------+--------+----+ | nick | post | id | +------+--------+----+ | a | tehe | 1 | | a | tehe 2 | 2 | | a …
Hahi
  • 121
  • 10
1
vote
1 answer

Update For all loans WHERE user_id=? in CASE when DATEDIFF() >0. Error 1242 and 1093

I have this query Update loans set fine = case when DATEDIFF(( (SELECT * from (SELECT loans.due_date FROM loans where users_id = 1) AS l1)) ,DATE(NOW())) > 0 THEN 1 ELSE fine END WHERE users_id =1; From table +-------------+ | Field …
Waka Waka
  • 95
  • 7
1
vote
1 answer

UPDATE column with join

SELECT lott.id as lottery_id,lott.abbr,lott.currency,payments.id as payment_id, payment_prizes.prize_id,prizes.name,prizes.currency as prizes_currency FROM lotteries lott JOIN lottery_payments payments ON …
Oyeme
  • 10,385
  • 4
  • 33
  • 59
1
vote
1 answer

How do I update a column in 3rd row the same as a column in 2nd row in MySQL?

I'm doing it this way: UPDATE products SET products_image = (SELECT products_image FROM products WHERE products_id = 2) WHERE products_id = 3; ...but get an error: ERROR 1093 (HY000):…
user198729
  • 55,886
  • 102
  • 239
  • 342
1
vote
2 answers

How to update a table using a select group by in a second one and itself as the data source in MySQL?

I can do this: SELECT t2.value + sum(t3.value) FROM tableA t2, tableB t3 WHERE t2.somekey = t3.somekey GROUP BY t3.somekey But how to do this? UPDATE tableA t1 SET speed = ( SELECT t2.value + sum(t3.value) FROM tableA t2,…
Jader Dias
  • 81,082
  • 147
  • 410
  • 611
1
vote
2 answers

Using EXISTS with MySQL

I have this simple query that works on all other database systems, but fails with MySQL: UPDATE points p SET p.userid = 5224 WHERE p.userid = 2532 AND NOT EXISTS ( SELECT 1 FROM points q WHERE q.userid = 5224 AND q.game =…
Milan Babuškov
  • 55,232
  • 47
  • 119
  • 176
1 2
3
4 5 6