Error 1093 Mysql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings mysql error code 1241 and policies of this site About Us Learn more about Stack Overflow mysql error 1093 delete the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags mysql temp table Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only
Mysql Error 1093 Update
takes a minute: Sign up MySQL Error 1093 - Can't specify target table for update in FROM clause up vote 332 down vote favorite 93 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 story_category mysql select into ON category_id=category.id); I tried to delete them executing: DELETE FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_id=category.id); But I get the next error: #1093 - You can't specify target table 'story_category' for update in FROM clause How can I overcome this? mysql subquery sql-delete mysql-error-1093 share|improve this question edited May 31 '15 at 9:35 shA.t 8,46641542 asked Sep 5 '08 at 10:03 Sergio del Amo 24.1k56125165 1 Related: stackoverflow.com/a/14302701/238419 –BlueRaja - Danny Pflughoeft Feb 22 '13 at 18:10 add a comment| 10 Answers 10 active oldest votes up vote 448 down vote accepted Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see DanDarc's answer In MySQL, you can't modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html You will need to stop using the nested subquery and execute the operation in two parts, or alternatively use a simple where clause. Below is from Baron Schwartz, published
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack mysql error 1064 Overflow the company Business Learn more about hiring developers or posting ads with us Stack
Mysql Update Syntax
Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community
Mysql Create Temporary Table
of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Update Error 1093 up vote 4 down vote favorite 1 This works in a table where http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause doc_id is the primary key: select count(*)+1 from doctor where exp > (select exp from doctor where doc_id='001'); +------------+ | count(*)+1 | +------------+ | 2 | +------------+ But when I'm using the same select query to set a field in the table, it reports the following error: update doctor set rank= ( select count(*)+1 from doctor where exp > (select exp from doctor where doc_id='001') ) where doc_id='001'; ERROR 1093 (HY000): http://stackoverflow.com/questions/5316576/mysql-update-error-1093 You can't specify target table 'doctor' for update in FROM clause I can't understand which target table reference it is talking of. Can someone explain? mysql sql sql-update mysql-error-1093 share|improve this question edited May 1 '12 at 17:03 Juan Mellado 11k52642 asked Mar 15 '11 at 18:57 user191776 add a comment| 3 Answers 3 active oldest votes up vote 10 down vote accepted This restriction is documented in the MySQL manual: Currently, you cannot update a table and select from the same table in a subquery. As a workaround, you can wrap the sub-query in another sub-query and avoid that error: update doctor set rank= (select rank from ( select count(*)+1 as rank from doctor where exp > (select exp from doctor where doc_id='001') ) as sub_query) where doc_id='001'; share|improve this answer answered Mar 15 '11 at 19:46 Ike Walker 33.4k95278 3 although it makes me wonder, is that restriction supposed to protect you from something that the workaround reveals or is it a technical problem? –chustar Mar 19 '11 at 2:00 add a comment| up vote 1 down vote You can't use a table you're updating in a subquery in the from clause. Try a join, or a double subquery :) share|improve this answer answered Mar 15 '11 at 19:00
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General Information Installing and Upgrading MySQL Using http://dev.mysql.com/doc/en/subquery-errors.html MySQL as a Document Store Tutorial MySQL Programs MySQL Server Administration https://bugs.mysql.com/bug.php?id=6980 Security Backup and Recovery Optimization Language Structure Globalization Data Types Functions and Operators SQL Statement Syntax Data Definition Statements ALTER DATABASE Syntax ALTER EVENT Syntax ALTER FUNCTION Syntax ALTER INSTANCE Syntax ALTER LOGFILE GROUP Syntax ALTER PROCEDURE Syntax ALTER SERVER Syntax ALTER TABLE Syntax ALTER mysql error TABLE Partition Operations ALTER TABLE Online Operations in MySQL Cluster ALTER TABLE and Generated Columns ALTER TABLE Examples ALTER TABLESPACE Syntax ALTER VIEW Syntax CREATE DATABASE Syntax CREATE EVENT Syntax CREATE FUNCTION Syntax CREATE INDEX Syntax CREATE LOGFILE GROUP Syntax CREATE PROCEDURE and CREATE FUNCTION Syntax CREATE SERVER Syntax CREATE TABLE Syntax CREATE TABLE ... LIKE mysql error 1093 Syntax CREATE TABLE ... SELECT Syntax Using FOREIGN KEY Constraints Silent Column Specification Changes CREATE TABLE and Generated Columns Secondary Indexes and Generated Virtual Columns Setting NDB_TABLE options in table comments CREATE TABLESPACE Syntax CREATE TRIGGER Syntax CREATE VIEW Syntax DROP DATABASE Syntax DROP EVENT Syntax DROP FUNCTION Syntax DROP INDEX Syntax DROP LOGFILE GROUP Syntax DROP PROCEDURE and DROP FUNCTION Syntax DROP SERVER Syntax DROP TABLE Syntax DROP TABLESPACE Syntax DROP TRIGGER Syntax DROP VIEW Syntax RENAME TABLE Syntax TRUNCATE TABLE Syntax Data Manipulation Statements CALL Syntax DELETE Syntax DO Syntax HANDLER Syntax INSERT Syntax INSERT ... SELECT Syntax INSERT DELAYED Syntax INSERT ... ON DUPLICATE KEY UPDATE Syntax LOAD DATA INFILE Syntax LOAD XML Syntax REPLACE Syntax SELECT Syntax SELECT ... INTO Syntax JOIN Syntax UNION Syntax Subquery Syntax The Subquery as Scalar Operand Comparisons Using Subqueries Subqueries with ANY, IN, or SOME Subqueries with ALL Row Subqueries Subqueries with EXISTS or NOT EXISTS Correlated Subqueries Subqueries in the FROM Clause Subquery Errors O
Federico Email Updates: Status: Verified Impact on me: None Category:MySQL Server: DML Severity:S2 (Serious) Version:4.1.7 OS:Microsoft Windows (Windows 2000) Assigned to: View Add Comment Files Developer Edit Submission View Progress Log Contributions [3 Dec 2004 10:20] Sanvido Federico Description: A particular Update with Inner Join and an 'exists' in the where clause with an inner join with the same table. a) mysql 4.1.3 beta version behaves correctly; b) mysql 4.1.7 gamma returns the following error : "SQLError# 1093 - You can't specify target table 't_sipa_stato' for update in FROM clause" How to repeat: ---------------------------------- -- Create Table ---------------------------------- CREATE TABLE t_sipa_stato ( COD_SIPA varchar(12) NOT NULL default '', COD_LATO char(1) NOT NULL default '', COD_BOTTIGLIA varchar(12) default NULL, FLG_ANOMALIA tinyint(1) NOT NULL default '0', PRIMARY KEY (COD_SIPA,COD_LATO), KEY IDX_LATI_BOTTIGLIA (COD_BOTTIGLIA), ) ENGINE=InnoDB; CREATE TABLE t_bottiglie ( COD_BOTTIGLIA varchar(12) NOT NULL default '', DES_BOTTIGLIA varchar(50) NOT NULL default '', COD_COLORE varchar(5) default NULL, PRIMARY KEY (COD_BOTTIGLIA), ) ENGINE=InnoDB; ---------------------------------- -- Update Test ---------------------------------- Update T_BOTTIGLIE Inner join T_SIPA_STATO On T_BOTTIGLIE.COD_BOTTIGLIA = T_SIPA_STATO.COD_BOTTIGLIA Set T_SIPA_STATO.FLG_ANOMALIA = 1 Where Exists ( Select * from T_BOTTIGLIE As BOTT Inner join T_SIPA_STATO As STAT On BOTT.COD_BOTTIGLIA = STAT.COD_BOTTIGLIA Where T_SIPA_STATO.COD_SIPA = STAT.COD_SIPA And T_SIPA_STATO.COD_LATO <> STAT.COD_LATO And T_BOTTIGLIE.COD_COLORE <> BOTT.COD_COLORE ) Suggested fix: Alternative SQL script to obtain the same result. [3 Dec 2004 12:08] Victoria Reznichenko Hi, Thank you for the report, but currently you can't update the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/mysql/en/UPDATE.html [4 Jul 2007 10:02] syed mohammed Subquery returns more than 1 row [4 Jul 2007 10:03] syed mohammed Subquery returns more than 1 row [4 Jul 2007 22:59] Jared S Ha! beat it, this bug should be closed... UPDATE sales AS t1, sales AS t2 SET t1.idproduct = 1 WHERE t2.id > 1 [20 Aug 2009 9:00] Victor Sergienko Still there in 5.1. update m set flag = 1 where id in (select max(grouped.id) from a as grouped group by year(grouped.timestamp)) No end-user reason why this shouldn't work. And it's a small excuse t