Mysql Error 1093
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 Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Error 1093 - Can't specify target table for update in FROM clause up vote 335 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 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,47641542 asked Sep 5 '08 at 10:03 Sergio del Amo 24.2k56125165 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 449 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 at Nabble: However, you can do multi-tabl
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 Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL Getting around error 1093 up vote 4 down vote http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause favorite Error 1093 states that you can't UPDATE or DELETE using a subquery if your subquery queries the table you are deleting from. So you can't do delete from table1 where id in (select something from table1 where condition) ; Ok, what's the best way to work around that restriction, (assuming you really do need to subquery to perform the delete and cannot eliminate the self referencing subquery http://stackoverflow.com/questions/4484779/mysql-getting-around-error-1093 entirely?) Edit: Here's the query for those who are interested: mysql> desc adjacencies ; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | parent | int(11) | NO | PRI | NULL | | | child | int(11) | NO | PRI | NULL | | | pathLen | int(11) | NO | | NULL | | +---------+---------+------+-----+---------+-------+ -- The query is going to -- tell all my children to -- stop thinking my old parents -- are still their parents delete from adjacencies where parent in ( -- ALL MY PARENTS,grandparents select parent from adjacencies where child=@me and parent!=@me ) -- only concerns the relations of my -- grandparents WHERE MY CHILDREN ARE CONCERNED and child in ( -- get all my children select child from adjacencies where parent=@me ) ; So what I've tried so far is creating a temporary table called adjsToDelete create temporary table adjsToRemove( parent int, child int ) ; insert into adjsToRemove... So now I have a collection of relations to delete, where the parent/child pairs each uniquely identify a row to delete. But how do I delete each pair from the adjacencies table now? It seems I n
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 http://stackoverflow.com/questions/tagged/mysql-error-1093 About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question Tagged Questions info http://dev.mysql.com/doc/en/subquery-errors.html newest frequent votes active unanswered ERROR 1093 (HY000): You can't specify target table '%s' for update in FROM clause learn more… | top users | synonyms 0 votes 1answer 24 views MySQL: mysql error shift all dates forward so max date = now Here is a sample of my results MySQL table: I would like to shift forward all dates for a specific user by the same interval, so that the highest date for that user is the current timestamp. I know ... mysql mysql-error-1093 asked Sep 13 at 18:28 BeetleJuice 8,5972823 0 votes 2answers 20 views MySql Update mysql error 1093 a column based on table join - Error 1093 UPDATE AMAZON SET AMARBELEG = ( Select Rechnungen.Rechnungsnummer from Rechnungen,Amazon where Rechnungen.Belegtext = Amazon.orderid ... mysql jointable mysql-error-1093 asked Jul 13 at 13:31 Locust 62 1 vote 1answer 474 views MySQL 5.7 error (1093: You can't specify target table ___ for update in FROM clause) - usual solution not working I have a table 'employees' and I'm trying to set some attributes (e.g. salary) to the same value as some other value in the table. My understanding of this error is that it can be avoided with the ... mysql mysql-error-1093 asked Nov 16 '15 at 5:57 filaments 185 1 vote 2answers 442 views MySQL select and update multiple rows from same table Generally I want to select rows from orders table in my database, that were created after some date, and then update office_id field for all of selected rows to the specific value 12. My MySQL version ... mysql sql select sql-update mysql-error-1093 asked Jul 1 '15 at 21:31 diman4eg 273 -2 votes 1answer 108 views #1093 - You can't specify target table 'rent' for update in FROM clause "UPDATE rent SET remai
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 MySQL as a Document Store Tutorial MySQL Programs MySQL Server Administration 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 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 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 Optimizing Subqueries Rewriting Subqueries as Joins UPDATE Syntax Transactional and Locking Statements START TRANSACTION, COMMIT, and ROLLBACK Syntax Statements That Cannot Be Rolled Back Statements That Cause an Implicit Commit SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax LOCK TABLES and UNLOCK TABLES Syntax Interaction of Table L