problem with DELETE FROM

Question

I try to delete some datas, and this is the model of my Tables :

 --------------------
|       TABLE1       |
 --------------------
| idnode |idattribute|
|____________________|
|6414224 | 109       |
|6912048 | 74        |
|5632108 | 109       |
|5097234 | 109       |
|9874625 | 9         |


 --------------------
|       TABLE2       |
 --------------------
| idnode | value     |
|____________________|
|6414224 | BLABLA    |
|6414224 | BLA       |
|6414224 | BL        |
|5097234 | 14524     |
|5097234 | hihi      |

I need to delete into the table 2, all 'idnode' wich have 'idattribute' = 109 in table 1.

So, this my query, but it doesn't work :

DELETE FROM table2 WHERE idnode IN 
(SELECT TAB1.idnode FROM TABLE2 as TAB2, TABLE1 as TAB1 where TAB2.idnode = TAB1.idnode and TAB1.idattribute = 109)

I obtain this error : You can't specify target table 'TABLE2' for update in FROM clause

Have you an idea ?

I think this post is related to your problem: http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause — Ashwin A, Aug 12 '11 at 07:44

score 0 · Accepted Answer · answered Aug 12 '11 at 07:42

0

DELETE FROM table2 WHERE idnode IN 
(SELECT idnode FROM TABLE1 WHERE idattribute = 109)

How about this?

answered Aug 12 '11 at 07:42

Shef

44,808
15
79
90

score 0 · Answer 2 · answered Aug 12 '11 at 07:45

0

DELETE table2
    FROM table1 JOIN table2 USING (idnode)
    WHERE table1.idattribute = 109

would be another alternative.

answered Aug 12 '11 at 07:45

glglgl

89,107
13
149
217

problem with DELETE FROM

2 Answers2