PostgreSQL multi-table query with name, first_name, and last_name

Question

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

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-09-16T01:47:00

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.

PostgreSQL multi-table query with name, first_name, and last_name

1 Answers