[FIXED] How do I change the value of one field in SQL record to a value from another table?

Issue

First of all, I'm sorry if this is a bit of a dumb question. I checked what's written in similarly phrased questions like "How do I update fields from one table with values from another table" but the content doesn't seem to match what I'm trying to do.

Let's say I have a table called site_users:

user_id	login	password	user_id2
2	user	password	1
7	access	xyz	2
11	otherlogin	abc	3
15	somebody	defg	4
22	user	qwert	5

Then I have a lot of other tables in the same database, that have some columns of various names that are actually corespondent to the "user_id" of the "site_users" table. There are no relations set or anything like that. I want to change the values in the fields of those other tables to user_id2. So let's say I have a table: user_options:

admin_id	perms1	perms2
2	1	12139389
7	1	13232111

I want to change it to:

admin_id	perms1	perms2
1	1	12139389
2	1	13232111

How can I do that? This is the first time I'm doing anything other than just simple mass changes of text with some regex :/

Solution

If i am understanding your question correctly you should be able to do following where table1 is top table and table2 is table you are trying to update:

update table2 t set admin_id = (select user_id2 from table1 where user_id = t.admin_id)

Answered By - Maxqueue