Delete Row in a Table Based on a Lookup Value from Another Table

Question

I have two (simplified) tables in a database.

Table: queuelist
'songID', 'lastplayed'
'7376', '12/01/2013'
'9322', '16/08/2012'

Table: songlist
'ID', 'artist'
'7376', 'Michael Jackson'
'2345', 'Nirvana'

'songID' and 'ID' are the same fields.

I'm given 'Michael Jackson' as an input and I want to delete the first row in 'queuelist' based on looking up 'ID' as the common field. I'm a MYSQL noob and have been trying examples but so far don't quite follow the syntax.

So far I have this...

DELETE S.songID
FROM queuelist Q,
(
JOIN songlist S
ON Q.songID = S.ID
)
WHERE S.artist = 'Michael Jackson'

Itay · Accepted Answer · 2013-08-29T14:01:57.023

1

You should use a sub-query in the WHERE clause rather than using JOIN.

DELETE FROM `queuelist`
WHERE       `songID` IN (SELECT `S`.`ID`
                         FROM   `songlist` `S`
                         WHERE  `S`.`artist` = 'Michael Jackson')

This will be the resulted data:

Table: queuelist
'songID', 'lastplayed'
'9322', '16/08/2012'

Table: songlist
'ID', 'artist'
'7376', 'Michael Jackson'
'2345', 'Nirvana'

edited Aug 29 '13 at 14:01

answered Aug 29 '13 at 12:14

Itay

16,601
2
51
72

Thanks for the reply. I get an indent error (Syntax error, Unexpected IDENT_QUOTED, expecting END_OF_INPUT or ';') with that on the first Q. Any ideas? – square_eyes Aug 29 '13 at 12:20
@square_eyes Edited. Try now – Itay Aug 29 '13 at 12:23
Syntax error has gone but now I get Error Code: 1054. Unknown column 'S.songID' in 'field list' – square_eyes Aug 29 '13 at 12:52
I think I found the issue. Should it be? ... `DELETE FROM `queuelist` WHERE `songID` = (SELECT `S`.`ID` FROM `songlist` `S` WHERE `S`.`artist` = 'Michael Jackson')` – square_eyes Aug 29 '13 at 12:57
This produces the new error `Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.` – square_eyes Aug 29 '13 at 12:59
Which I don't really understand. Because there is a 'Where' clause. Do I have to disable safe mode? – square_eyes Aug 29 '13 at 13:16
I found a post that suggests using a limit constraint. Would this help? – square_eyes Aug 29 '13 at 13:18
@square_eyes Edited my query to the correct column name. Why isn't the songID column a key? Any way, you need to write `SET SQL_SAFE_UPDATES=0;` on your DB. For further reading [MySQL error code: 1175 during UPDATE in MySQL Workbench](http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench) – Itay Aug 29 '13 at 13:19
I'd rather not turn off Safe Updates if possible. To confuse matters queuelist has a column called 'ID' which is the key. It's not the same as songID or ID from the other table. I excluded it from the simplified example as I didn't think it would be used. FYI I have no control over the column names unfortunately. – square_eyes Aug 29 '13 at 13:27
@square_eyes Make `queueList`.`songId` a foreign key to `songList`.`id` – Itay Aug 29 '13 at 13:30
@square_eyes ley us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/36462/discussion-between-itay-and-square-eyes) – Itay Aug 29 '13 at 15:26

Delete Row in a Table Based on a Lookup Value from Another Table

1 Answers1