Delete Query with SELECT statement

Question

I have been using 2 tables like this

tbl_songs
id | track_id | dummy_name

tbl_tracks 
id | song_id

I have duplicate dummy_name but my first record's value from set of duplicate records is updated to tbl_tracks like this.

tbl_songs s
id | track_id | dummy_name
 1 |       12 | A
 2 |          | A
 3 |          | A
 4 |      2   | B
 4 |          | B

tbl_tracks t
 id | song_id 
 2  | 4
 12 | 1

By Inner join relationship I have updated tbl_songs 's track_id column to find the duplicate records..

NOTE I want to delete those records that are with same dummy_name (i.e duplicate recods) and have track_id = '' or I can say that are not related with tbl_tracks.

My tried sql for selecting records

SELECT a.id as aid, a.dummy_name as adn, b.id as bid, b.dummy_name as bdn
FROM tbl_songs a
LEFT JOIN tbl_songs b ON a.dummy_name = b.dummy_name
WHERE a.track_id != '' AND  a.dummy_name != '' AND a.id <> b.id

To delete these records

DELETE FROM tbl_songs where EXISTS (
SELECT *
FROM tbl_songs a
LEFT JOIN tbl_songs b ON a.dummy_name = b.dummy_name
WHERE a.track_id != '' AND  a.dummy_name != '' AND a.id <> b.id)

Error: You can't specify target table 'tbl_songs' for update in FROM clause

http://stackoverflow.com/questions/4562787/how-to-delete-from-select-in-mysql — Fabio, Jul 30 '13 at 16:57
`DELETE FROM tbl_songs where id IN ( SELECT b.id FROM tbl_songs a LEFT JOIN tbl_songs b ON a.dummy_name = b.dummy_name WHERE a.track_id != '' AND a.dummy_name != '' AND a.id <> b.id)` Ends with same error. I have tried it before — Sankalp, Jul 30 '13 at 17:02
I would question your schema while at it. You have basically created a circular reference. Why does `tbl_tracks` need to reference `tbl_songs` when `tbl_songs` already has a reference to `tbl_tracks`? — Mike Brant, Jul 30 '13 at 17:13
tbl_tracks with `song_id` was my first reference. And it was absolutely correct. I referred second tbl_songs with `track_id` just to remove duplicate records. I will drop the column once I will finish my requirement. — Sankalp, Jul 30 '13 at 17:17
greetings OP. If an answer helped you find the problem and for you to solve it, it is customary to tick it. cheers — d'alar'cop, Aug 01 '13 at 14:51

score 1 · Answer 1 · answered Jul 30 '13 at 17:08

1

If I understand it well, you need to delete from tbl_songs all records such as track_id = '' if, and only if, there is an other record with the same dummy_name but having track_id != ''

DELETE FROM tbl_song 
    WHERE track_id = '' 
    AND dummy_name IN (SELECT dummy_name FROM tbl_song WHERE track_id != '')

answered Jul 30 '13 at 17:08

Sylvain Leroux

50,096
7
103
125

I won't doubt if it would be so simple. I had this in my mind. I have some records in tbl_songs that are not duplicate but at same time they are not linked with tbl_tracks – Sankalp Jul 30 '13 at 17:12
@Sankalp I don't get that later case from your example. Could you update it? – Sylvain Leroux Jul 30 '13 at 17:16
I have some records in tbl_songs with dummy_name that are not repetative. and completely not linked with tbl_track table. That are unique with itself with no relation to tbl_tracks. I hope I am clear – Sankalp Jul 30 '13 at 17:19

score 0 · Answer 2 · answered Jul 30 '13 at 17:16

The error is a normal feature of MySQL. (i.e. you cannot be affecting a table and selecting over it in the same statement).

You can create a temp table with the IDs from the songs table (an incrementing int or whatever) then delete using:

DELETE ... WHERE id IN (SELECT id FROM temp)

Or something similar to this. Reasonable?

Delete Query with SELECT statement

2 Answers2