How remove rows by id, got by group_concat

Question

I want to remove some rows from table.

First, i tried to use subquery, but I can't remove data from table, when use the same table in subquery.

I tried do that in another way.

set @list_id_remove = (select group_concat(mra.media_id) from movie AS m
right join media AS mra ON m.id = mra.media_id
where m.id is null);

delete from media
where media_id IN (@list_id_remove);

But in this case query remove only 1 row. I think trouble - is that group_concat remove string and I need list of integers.

Gordon Linoff · Accepted Answer · 2014-02-07T18:01:25.103

4

You seem to want to delete from media where the corresponding record doesn't exist in movie. You can do this as a single query.

delete from media 
    where not exists (select 1 from movie m where m.id = media.media_id);

It does seem strange to me that the field movie.id would correspond to media.media_id, but that is how your original query is written.

EDIT:

As for your question, you can write the delete as:

delete from media
    where find_in_set(media_id, @list_id_remove) > 0;

edited Feb 07 '14 at 18:01

answered Feb 07 '14 at 16:13

Gordon Linoff

1,198,228
53
572
709

Thanks, first query works, but add to query "where" before m.id = media.media_id – yAnTar Feb 07 '14 at 17:11
Servers in general will HEAR YOUR QUERIES BETTER IF YOU [CAPITALIZE](http://stackoverflow.com/questions/9426138/why-are-the-queries-in-sql-mostly-written-in-capital-letters) YOUR CODE ;) – leonardo_assumpcao Feb 07 '14 at 17:26
@yAnTar . . . Thank you. – Gordon Linoff Feb 07 '14 at 18:01

How remove rows by id, got by group_concat

1 Answers1