SQL error: DELETE and SELECT FROM conflict

Question

I've a problem with the following SQL query:

DELETE FROM table1 WHERE uid =
(SELECT table1.uid from table1 INNER JOIN table2 ON table2.user = table1.uid
INNER JOIN table3 ON table3.uid = table2.seminar WHERE table3.end_date < CURDATE()))

The error is: You can't specify target table 'table1' for update in FROM clause

Does anybody have an idea how to fix that?

the identifier table1 seems to be ambigious, because you use it in your outer query (`DELETE`) as well as in the inner query, you would need to give it an alias in the inner query and use that in your conditions to make it work — DrCopyPaste, Sep 30 '13 at 15:42

score 2 · Answer 1 · answered Sep 30 '13 at 15:41

This may not be the most elegant way, but you can select your criteria into a temp table then DELETE FROM WHERE IN #MyTempTable

SELECT table1.uid 
INTO #MyTemp
from table1 INNER JOIN table2 ON table2.user = table1.uid
INNER JOIN table3 ON table3.uid = table2.seminar WHERE table3.end_date < CURDATE())


DELETE FROM table1 
WHERE uid IN
(SELECT uid from #MyTemp)

DROP TABLE #MyTemp

score 1 · Answer 2 · answered Sep 30 '13 at 15:40

1

You can alternative join the tables.

DELETE  table1
FROM    table1 
        INNER JOIN table2 
            ON table2.user = table1.uid
        INNER JOIN table3 
            ON table3.uid = table2.seminar
WHERE   table3.end_date < CURDATE()

answered Sep 30 '13 at 15:40

John Woo

258,903
69
498
492

[looking at you comment](http://stackoverflow.com/questions/19098279/sql-error-delete-and-select-from-conflict/19098331#comment28236629_19098331) You need to use `FIND_IN_SET()` since the values are separated with comma. eg `WHERE table3.end_date < CURDATE() AND ( FIND_IN_SET('67', table1.group) > 0 AND FIND_IN_SET('69', table1.group) > 0 )` if the value of `table1.group` has spaces in between, you also need to remove it. eg. `FIND_IN_SET('67', REPLACE(table1.group, ' ', '') > 0` – John Woo Sep 30 '13 at 16:26

score 0 · Accepted Answer · answered Sep 30 '13 at 15:40

0

Try this:-

DELETE  table1 FROM    table1 INNER JOIN table2 ON table2.user = table1.uid
        INNER JOIN table3 ON table3.uid = table2.seminar
WHERE   table3.end_date < CURDATE()

answered Sep 30 '13 at 15:40

Rahul Tripathi

168,305
31
280
331

before you have formatted your answer. hmmm the formatting looks interesting. – John Woo Sep 30 '13 at 15:41
Just wanted to quick. But you won by seconds ;) – Rahul Tripathi Sep 30 '13 at 15:42
you both were very fix... thanks for your answers! now it's working! Thank you both very much! – Adrian Sep 30 '13 at 15:50
one more question: is it possible to add at the end "AND table1.group = '67, 69'", if i want only to delete elements with the group 69 (!) and 67? to be exactly: all elements have the group 67, only some of the the group 69. But in the DB they are comma separated as a string – Adrian Sep 30 '13 at 16:06

granadaCoder · Answer 4 · 2013-09-30T16:15:10.060

0

My preferred syntax is:

Delete deleteAlias
/* Select *         */
from table1 deleteAlias
where exists ( select null from table1 innerTable1 INNER JOIN table2 ON table2.user = innerTable1.uid
INNER JOIN table3 ON table3.uid = table2.seminar WHERE table3.end_date < CURDATE())   

AND deleteAlias.uid = innerTable1.uid

   )

The subtle benefit if this approach is you can substitute a simple "select *" to see what will be deleted before you run the delete.

/* Delete deleteAlias */
Select *
from table1 deleteAlias
where exists ( select null from table1 innerTable1 INNER JOIN table2 ON table2.user = innerTable1.uid
INNER JOIN table3 ON table3.uid = table2.seminar WHERE table3.end_date < CURDATE())   

AND deleteAlias.uid = innerTable1.uid

   )

edited Sep 30 '13 at 16:15

answered Sep 30 '13 at 15:43

granadaCoder

26,328
10
113
146

All the T1's and T2's start running together. By giving the "outside" table a clear alias (table1 deleteAlias in this example), when you use the exists clause you see clearly the "inside the exists clause" joining to the "outside of the exists clause" .... ( " AND deleteAlias.uid = innerTable1.uid " ) in this example. Remember, the bigger cost of software development is ~maintenance~, not initial creation..........aka, somebody needs to be able to see what you did down the road........even if that someone is you. – granadaCoder Sep 30 '13 at 15:46

score 0 · Answer 5 · edited May 23 '17 at 12:13

0

You can't modify or delete a table you're using in a temporary mode in the select clause. You could see explanation and alternatives here: MySQL Error 1093 - Can't specify target table for update in FROM clause

edited May 23 '17 at 12:13

Community

1
1

answered Sep 30 '13 at 15:44

Lolito

413
3
9

SQL error: DELETE and SELECT FROM conflict

5 Answers5