How to delete from select in same table MySQL

Question

Hi I have a rather complicated query that I've been using for a long time in SQLite. It starts like this:

delete FROM 
   verbal_sets 
WHERE verbal_sets.verbal_set_id IN( 
   SELECT 
       F.verbal_set_id 
   FROM verbal_sets AS F WHERE Exists( 
       SELECT 
         knowledge_id, phrase_id,first_entity_text, second_entity_text,verbal_relation_text, Count(verbal_set_id) 
       FROM
        (...)

I'm now creating a MySQL supported version of the same software but MySQl doesn't like this query. It returns the error:

[Err] 1093 - You can't specify target table 'verbal_sets' for update in FROM clause

I have no experience with MySQL specifics. Can anyone point me what must be done with this query?

possible duplicate of [Mysql error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/q/45494/) and [**many** others](http://stackoverflow.com/search?q=%5bmysql%5d%20%2b%22you%20can%27t%20specify%20target%20table%22%20%2b%22for%20update%20in%22%20%2bdelete). — outis, Jul 17 '12 at 22:17
Can you post the MySQL version of the query if it's any different? — Chris C, Jul 17 '12 at 22:18
@ChrisC - The MySQL query is what I'm trying to build. This is the SQLite query. — Miguel E, Jul 18 '12 at 10:50

score 0 · Answer 1 · edited Jun 04 '16 at 12:48

0

I hope this will help you,

delete FROM 
   verbal_sets 
WHERE verbal_set_id IN( 
   SELECT * 
    From( 
       SELECT knowledge_id, 
              phrase_id,
              first_entity_text, 
              second_entity_text, 
              verbal_relation_text, 
              Count(verbal_set_id) 
       FROM  verbal_sets
       ) as c
)

edited Jun 04 '16 at 12:48

reza.cse08

5,938
48
39

answered Jun 04 '16 at 11:23

Kritika Bajpai

1
1

Actually, I was dealing with the same problem and i got this solution. – Kritika Bajpai Jun 04 '16 at 11:42

score -1 · Answer 2 · answered Jul 17 '12 at 22:24

-1

Just skip a few lines:

DELETE FROM 
   verbal_sets 
WHERE Exists( 
       SELECT 
         knowledge_id, phrase_id,first_entity_text, second_entity_text,verbal_relation_text, Count(verbal_set_id) 
       FROM

answered Jul 17 '12 at 22:24

Puggan Se

5,738
2
22
48

How to delete from select in same table MySQL

2 Answers2