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
663
votes
16 answers

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN …
Sergio del Amo
  • 71,609
  • 66
  • 148
  • 177
403
votes
11 answers

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

I have a simple mysql table: CREATE TABLE IF NOT EXISTS `pers` ( `persID` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(35) NOT NULL, `gehalt` int(11) NOT NULL, `chefID` int(11) DEFAULT NULL, PRIMARY KEY (`persID`) ) ENGINE=MyISAM …
CSchulz
  • 10,102
  • 9
  • 54
  • 107
72
votes
4 answers

SQL DELETE with JOIN another table for WHERE condition

I have to delete rows from guide_category that have no relation with guide table (dead relations). Here is what I want to do, but it of course does not work. DELETE FROM guide_category AS pgc WHERE pgc.id_guide_category IN (SELECT…
hsz
  • 136,835
  • 55
  • 236
  • 297
52
votes
11 answers

Insert and set value with max()+1 problems

I am trying to insert a new row and set the customer_id with max()+1. The reason for this is the table already has a auto_increatment on another column named id and the table will have multiple rows with the same customer_id. With this: INSERT INTO…
Cozzy
  • 531
  • 1
  • 4
  • 4
42
votes
2 answers

Delete - I can't specify target table?

Why this query doesn't work? DELETE FROM recent_edits WHERE trackid NOT IN (SELECT DISTINCT history.trackid FROM history JOIN recent_edits ON history.trackid=recent_edits.trackid GROUP BY recent_edits.trackid) I get this message…
markzzz
  • 44,504
  • 107
  • 265
  • 458
40
votes
7 answers

SQL UPDATE with sub-query that references the same table in MySQL

I'm trying to update a column's value in a bunch of rows in a table using UPDATE. The problem is that I need to use a sub-query to derive the value for this column, and it depends on the same table. Here's the query: UPDATE user_account student SET…
egervari
  • 21,108
  • 30
  • 115
  • 171
36
votes
5 answers

How do I lock read/write to MySQL tables so that I can select and then insert without other programs reading/writing to the database?

I am running many instances of a webcrawler in parallel. Each crawler selects a domain from a table, inserts that url and a start time into a log table, and then starts crawling the domain. Other parallel crawlers check the log table to see what…
T. Brian Jones
  • 11,630
  • 19
  • 67
  • 109
26
votes
6 answers

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

I tried: UPDATE giveaways SET winner = '1' WHERE ID = (SELECT MAX(ID) FROM giveaways) But it gives: #1093 - You can't specify target table 'giveaways' for update in FROM clause This article seems relevant but I can't adapt it to my query. How can…
Eray
  • 6,597
  • 15
  • 62
  • 109
10
votes
5 answers

Select from same table as an Insert or Update

Clearly the following is incorrect. INSERT INTO `aTable` (`A`,`B`) VALUES((SELECT MAX(`A`) FROM `aTable`)*2),'name'); I get the value: SQL query: INSERT INTO `aTable` (`A`, `B` ) VALUES ( ( SELECT MAX(`A`) FROM `aTable` ) *2 ,…
Issac Kelly
  • 5,999
  • 6
  • 40
  • 50
9
votes
4 answers

Deleting a row based on the max value

How can I structure a mySQL query to delete a row based on the max value. I tried WHERE jobPositonId = max(jobPostionId) but got an error?
Robert de Klerk
  • 604
  • 3
  • 12
  • 23
7
votes
1 answer

Handling database integrity

I'm introducing database integrity using innodb constraints in the next version of my application. Everything goes well, but some of my tables have records with deleted references (dead records) and because of them I can't add constraints to the…
Silver Light
  • 37,827
  • 29
  • 116
  • 159
7
votes
4 answers

Mysql SELECT inside UPDATE

UPDATE forms SET pos = (SELECT MIN(pos)-1 FROM forms) WHERE id=$id This doesn't work, error message: **You can't specify target table 'form' for update in FROM clause** I hope it's clear: I want to get the minimal element-1 from the same table…
Dan
  • 48,995
  • 35
  • 110
  • 141
6
votes
1 answer

how to delete duplicate rows from a table in mysql

I need to delete duplicate record from table in mysql. So i have a table name "employee" fields are empid, empname, empssn for getting duplicate record i have written a query SELECT COUNT(empssn), empssn FROM employee GROUP BY empssn…
user394486
  • 61
  • 1
  • 2
5
votes
1 answer

MySQL batch update

I have 2 tables (MySQL) data_details accounts_invoices Ideally every data_details should have an accounts_invoices id. (data_details has a foreign key with accounts_invoices's primary key) For some reason there are data_details records where there…
sameera207
  • 16,117
  • 18
  • 81
  • 143
5
votes
5 answers

MySQL INSERT Using Subquery with COUNT() on the Same Table

I'm having trouble getting an INSERT query to execute properly, and I can't seem to find anything on Google or Stack Overflow that solves this particular issue. I'm trying to create a simple table for featured entries, where the entry_id is saved to…
jlengstorf
  • 427
  • 1
  • 5
  • 10
1
2 3 4 5 6