Having trouble creating set-based query to create table data instead of doing it procedurally

Given table settings with unique key of id+appId

id	appId	name
1	app1	setting1
2	app1	setting2

I have created new entries and the table looks like so

id	appId	name
1	app1	setting1
2	app1	setting2
3	app2	setting1
4	app2	setting2

Then given table user_settings that connects settings to a user

userId	settingsId
1	1
1	2
2	1

I need to add all of the new settings that were added for app2 to every user that currently has a setting for app1. The user should only get the settings that have matching names though.

I don’t know if that’s clear so I’ll say it this way as well: If the user has a setting of a given name for app1 then it should get the setting with the same name for app2.

Is there a way to do this with set-based queries? Or do I have do this procedurally? I’m not great with much but basic SQL unfortunately.

So the user_settings table should end up being

userId	settingsId
1	1
1	2
2	1
1	3
1	4
2	3

Thank you for any help.

Answer

here is how you can do it:

select us.userid , s2.id
from user_settings us
join settings s1
   on s1.appid = 'app1'
   and us.settingsId = s1.id
join settings s2
 on s2.appid = 'app2'
 and s2.name = s1.name

you can use the same query to insert into your table.db<>fiddle here