delete join nested query

Question

i have table VIDEO with cols (url, title, desc, ...) and table FAVORITE with cols(fav_url, thumb).

when i try to delete rows from both tables using:

delete from video join favorite on video.url=favorite.fav_url
  where url in (select url from video where title like '%thumb%')

i get error.

is there any way to do this job executing one query?

Eugene Yarmash · Answer 1 · 2011-04-22T08:07:34.670

2

Try:

DELETE t1, t2 FROM video t1 JOIN favorite t2 ON t1.url=t2.fav_url
WHERE url IN
    (SELECT url FROM (SELECT * FROM video) t3 WHERE title LIKE '%thumb%')

edited Apr 22 '11 at 08:07

answered Apr 21 '11 at 20:49

Eugene Yarmash

142,882
41
325
378

#1093 - You can't specify target table 't1' for update in FROM clause – daniel.tosaba Apr 21 '11 at 22:07

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

EDIT TRY THIS NOW

YOU CANT ACTUALLY DO THIS TYPE OF DELETE QUERY WHEN YOU HAVE A SUB QUERY LIKE YOU DO. Read this article which explains more in detail.

MySQL Error 1093 - Can't specify target table for update in FROM clause

DELETE video, favorite 
    FROM video
    JOIN favorite 
        ON video.url = favorite.fav_url
    JOIN (SELECT DISTINCT url 
                  FROM video 
                  WHERE title like '%thumb%') tt ON video.url = tt.url

edited May 23 '17 at 12:03

Community

1
1

answered Apr 21 '11 at 20:51

Robbie Tapping

2,516
1
17
18

#1093 - You can't specify target table 'V' for update in FROM clause – daniel.tosaba Apr 21 '11 at 22:07
WHAT THE? Did you use the exact code above? did you have anything in the Query window at the same time? I just wrote a query nearly identitical and got no error at all. – Robbie Tapping Apr 21 '11 at 22:12
What version of mysql are you using? – Robbie Tapping Apr 21 '11 at 22:33
ur edited version: #1093 - You can't specify target table 'video' for update in FROM clause – daniel.tosaba Apr 21 '11 at 22:44
Found a URL that i have added to my answer, it explains why you cant do that type of update when you have a Subquery like you do. – Robbie Tapping Apr 21 '11 at 22:47
I have reposted the SQL with a change to the IN WHERE Statement. – Robbie Tapping Apr 21 '11 at 22:54
;) Glad to hear, got there at the end. – Robbie Tapping Apr 21 '11 at 23:31

delete join nested query

2 Answers2