So this is my table structures if it helps:
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?
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.
a.id instead.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.
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;
