How to insert values into table based off values of another table in SQL?

So in SQL, I have a table albums that has (album_id, album_name, year) I also have a table songs that has (song_id, song_name, album_name).

I used an alter table statement to add a column to songs called album_id

For each song that belongs to an album, I want to add its respective album_id to that row

I’m not sure how to do this. I’ve done

UPDATE songs
SET songs.album_id = (select albums.album_id FROM albums WHERE songs.album_name = albums.album_name);

However that subquery returns more than one row and gives me an error. I’ve also tried adding distinct in the subquery and adding a group by albums.album_id in the subquery.

There are multiple songs in the songs table that belong to the same album. Each album only appears once in the album table.

Answer

Limit it to one if it is always the same album_id

UPDATE songs
SET songs.album_id = (select albums.album_id 
                      FROM albums 
                      WHERE songs.album_name = albums.album_name 
                      LIMIT 1);

A grouup by makes only then sense if you wnat to add more information to a description field