원인
MySQL 은 Oracle 과는 달리 UPDATE 나 DELETE 시 자기 테이블의 데이타를 바로 사용 못하므로 아래와 같은 SQL 을 실행시 1093 에러가 발생함.
DELETE FROM cwd_group WHERE id IN (SELECT DISTINCT a.id ext_id FROM cwd_group a JOIN cwd_group b ON a.group_name=b.group_name JOIN cwd_directory d ON d.id=a.directory_id WHERE a.directory_id != b.directory_id AND directory_name = 'My JIRA Server');
SQL
처리
Sub Query 를 하나 더 넣고 sub query 결과를 임시 테이블로 만든후에 실행하면 해결됨.
아래 예제처럼 SELECT ext_id FROM 뒤에 오는 sub query 의 결과를 tmp 라는 임시 테이블에 저장하여 사용
DELETE FROM cwd_group WHERE id IN (SELECT ext_id FROM (SELECT DISTINCT a.id ext_id FROM cwd_group a JOIN cwd_group b ON a.group_name=b.group_name JOIN cwd_directory d ON d.id=a.directory_id WHERE a.directory_id != b.directory_id AND directory_name = 'My JIRA Server') tmp) ;
SQL
자기 테이블의 sub query 결과를 받아서 반영할 경우 아래와 같은 서브 쿼리 대신 를 실행할 경우 1093 에러를 접하게 된다.
UPDATE tbl1 set name = concat(name, 'aa') where id in (select id from tbl1 where name is not null);
CODE
단일 테이블일 경우 아래와 같이 sub query 의 결과를 임시 테이블(tbl1_result)로 만들어서 한 번 더 감싸도록 서브 쿼리를 작성하면 됨.
UPDATE tbl1 set name = concat(name, 'aa') where id in ( select tbl1_alias.nid from ( select id nid from tbl1 where name is not null ) tbl1_alias );
CODE
Ref
- //dev.mysql.com/doc/refman/5.5/en/subquery-errors.html
- //stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause
Problem/Motivation
Clicking "Clear translations of all listed equal translations" on the equal translation remover leads to:
SQLSTATE[HY000]: General error: 1093 You can't specify target table 'locales_target' for update in FROM clause: DELETE FROM "locales_target" WHERE (lid IN (SELECT ls.lid FROM "locales_source" ls INNER JOIN "locales_target" lt WHERE ls.lid=lt.lid AND CONVERT(ls.source USING utf8) = CONVERT(lt.translation USING utf8) AND lt.customized = :onlyCustomized)); Array ( [:onlyCustomized] => 1 )
Datenbank
Version
5.7.38-1
System
MySQL, Percona Server, or equivalent
Steps to reproduce
See above
Proposed resolution
Remaining tasks
User interface changes
API changes
Data model changes
Support from Tag1 fosters the development of Drupal
- View
- Add Comment
- Files
- Developer
- Edit Submission
- View Progress Log
- Contributions
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.
Let us first create a table −
mysql> create table DemoTable1597 -> ( -> Marks int -> ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −
mysql> insert into DemoTable1597 values(45); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1597 values(59); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1597 values(43); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1597 values(85); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1597 values(89); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −
This will produce the following output −
+-------+ | Marks | +-------+ | 45 | | 59 | | 43 | | 85 | | 89 | +-------+ 5 rows in set (0.00 sec)Here is the query to remove ERROR 1093 (HY000). We are deleting the lowest value here −
mysql> delete from DemoTable1597 -> where Marks=( -> select lowestMarks from ( select min(Marks) as lowestMarks from DemoTable1597 ) as deleteRecord -> ) limit 1; Query OK, 1 row affected (0.11 sec)Let us check the table records once again −
mysql> select * from DemoTable1597;This will produce the following output −
+-------+ | Marks | +-------+ | 45 | | 59 | | 85 | | 89 | +-------+ 4 rows in set (0.00 sec)
Updated on 16-Dec-2019 07:20:03
- Related Questions & Answers
- MySQL Error ERROR 1099 (HY000): Table was locked with a READ lock and can't be updated
- Fix: ERROR 1396 (HY000): Operation CREATE USER failed in MySQL?
- Fix Error in MySQL syntax while creating a table column with name “index”?
- How to fix the incorrect datetime value while inserting in a MySQL table?
- Fix for MySQL ERROR 1406: Data too long for column” but it shouldn't be?
- Fix ERROR 1064 (42000) while creating a database in MySQL?
- How can we extract a substring from the value of a column in MySQL table?
- Fix Error 1136: Column count doesn't match value count at row 1?
- How can I use MySQL subquery as a table in FROM clause?
- How can we update MySQL table after removing a particular string from the values of column?
- How can we remove a column from MySQL table?
- Reorder keys after deleting a record from MySQL table?
- How can I update a field in a MySQL database table by adding a value in the second table with a value from the first table?
- Fix Drop table view #1051 unknown table error in MySQL
- Update only a single value from a MySQL table where select from same table ordered in descending order?