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

3703 views sql
1

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

answered question

1 Answer

4

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.

posted this

Have an answer?

JD

Please login first before posting an answer.