I have two tables in my PostgreSQL database.
One table, call it 'Archive', has a column 'name' which are two-word first-name and last-name, e.g. 'John Smith' and an empty column which wants to be full of 'user_id'.
The second table, call it 'User Accounts', has three columns, one 'first_name' and one 'last_name' and one 'id'.
My goal is to implement a query that uses the column in Archive to select for the corresponding first_name and last_name in User Accounts and return to Archive the 'id' into 'user_id' column
You can use a
join. For instance:
select a.*, ua.id as user_id from archive a left join user_accounts ua on a.name = ua.first_name || ' ' || ua.last_name;
If you actually want to update the column:
update archive a set user_id = ua.id from user_accounts ua where a.name = ua.first_name || ' ' || ua.last_name;
Note that name matching can be quite tricky. You may not get as many matches as you expect. If this turns out to be an issue, ask another question. Include sample data and desired results in the question.