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

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
– Sankalp, Commented Jul 30, 2013 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
– Mike Brant, Commented Jul 30, 2013 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
– Sankalp, Commented Jul 30, 2013 at 17:17

Sylvain Leroux · Accepted Answer · 2013-07-30 17:08:05Z

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, 2013 at 17:08

Sylvain Leroux

52.4k8 gold badges114 silver badges136 bronze badges

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

3 Comments

Sankalp Over a year ago

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

Sylvain Leroux Over a year ago

@Sankalp I don't get that later case from your example. Could you update it?

Sankalp Over a year ago

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

d'alar'cop · Accepted Answer · 2013-07-30 17:16:40Z

0

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?

answered Jul 30, 2013 at 17:16

d'alar'cop

2,3531 gold badge15 silver badges18 bronze badges

Collectives™ on Stack Overflow

Delete Query with SELECT statement

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related