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

debugcn Published at Dev

square_eyes

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

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'

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-20

Comments

0 comments

From Dev

How to access a row on a table based on a value from another table?

From Dev

Row value from another table

From Dev

delete a row from table based on a specific association

From Dev

delete a row from table based on a specific association

From Dev

delete row from table where column does not exist in another table

From Dev

Delete from one table unless row is referenced in another table

From Dev

How to delete rows from a table based on a join with another table?

From Dev

Trigger with lookup from another table

From Dev

SQL - Delete row based on number of rows in another table

From Dev

Joining row from one table with the sum value from another table

From Dev

Change color based on value from another table

From Dev

SQL - Insert Into Select query with lookup of value from another table

From Dev

How to add a value from one table to a particular row in another table?

From Dev

delete rows based in the value of the previous row in the same table

From Dev

Delete row in a table by clicking cell in another table

From Dev

Read data from one table to another based on Like "row_value"

From Dev

Updating column in a table based on the value from another table

From Dev

Selecting a column name dynamically (based on another table row's value)

From Dev

link a value from one table to another and slice one table based on columns from another table in sql

From Dev

Replacing table value based on another table value

From Dev

creating and using lookup table based on AD value

From Dev

SQL Hide/Show rows based on row count from another table

From Dev

Delete a row from Parse Table

From Dev

Delete a row from sqlite table

From Dev

Delete row from dynamic table

From Dev

Delete row from table conditional on a related table

From Dev

Update table with another table based on cell value

From Dev

Get data from a table based on a table name as a field value from another table

From Dev

Getting a list value for every row (list of ids from another table)

Related Related

Article