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 :/
CodePudding user response：
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)