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 · Accepted Answer · 2011-04-22 08:07:34Z

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, 2011 at 8:07

answered Apr 21, 2011 at 20:49

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

daniel.tosaba Over a year ago

#1093 - You can't specify target table 't1' for update in FROM clause

Community · Accepted Answer · 2017-05-23 12:03:12Z

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, 2017 at 12:03

CommunityBot

11 silver badge

answered Apr 21, 2011 at 20:51

Robbie Tapping

2,5561 gold badge17 silver badges18 bronze badges

7 Comments

daniel.tosaba Over a year ago

#1093 - You can't specify target table 'V' for update in FROM clause

Robbie Tapping Over a year ago

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 Over a year ago

What version of mysql are you using?

daniel.tosaba Over a year ago

ur edited version: #1093 - You can't specify target table 'video' for update in FROM clause

Robbie Tapping Over a year ago

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.

|

Collectives™ on Stack Overflow

delete join nested query

2 Answers 2

1 Comment

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related