CODEWITHSUNDEEP
mysqlsql
coyotecipher
coyotecipher

Reputation: 15

MySQL update values from another table

So this is my table structures if it helps:

enter image description here

 UPDATE songs
-> SET artist_id =
-> (SELECT artist_id FROM artists WHERE artists.name = songs.artist);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 27  Changed: 0  Warnings: 0

My code keeps matching rows but never ends up changing anything in the actual table

Any ideas on why it's not working?

Upvotes: 0

Views: 82

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520928

You may try using an update join here:

UPDATE songs s
LEFT JOIN artists a
    ON a.name = s.artist
SET artist_id = a.artist_id;

Upvotes: 1

erik258
erik258

Reputation: 16285

classic use case for an update join.

UPDATE songs
JOIN artists ON song.artist = artist.name
SET songs.artist_id = artists.artist_id;

None of these syntaxes, however, will change the fact that the data is already up to date.

Query OK, 0 rows affected (0.00 sec) Rows matched: 27 Changed: 0 Warnings: 0

27 songs rows were matched. The artist_id of each was already set to the corresponding artists.artist_id. So O rows were affected or changed. No warnings were generated. Just because a query matched rows doesn't mean it changed them.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

This code basically looks correct. I would write it as:

UPDATE songs s
    SET artist_id = (SELECT a.artist_id 
                     FROM artists a 
                     WHERE a.name = s.artist
                    );

If this query returns an error, then artist_id is not in artists. You probably want a.id.

If this query does not update anything, then the artist_ids already have the same value.

Upvotes: 1

Related Questions