How to update multiple columns on PostgreSQL with values from another table [duplicate]

Question

I have the following update statement:

UPDATE users u
SET last_login=(
        SELECT created_on
        FROM user_login_log log
        WHERE log.user_id = u.id
        ORDER BY log.created_on DESC
        LIMIT 1
    )

but.. what if I have more values that need to be updated? Do I need to:

UPDATE users u
SET last_login=(
        SELECT created_on
        FROM user_login_log log
        WHERE log.user_id = u.id
        ORDER BY log.created_on DESC
        LIMIT 1
    ),
    last_ip=(
        SELECT ip
        FROM user_login_log log
        WHERE log.user_id = u.id
        ORDER BY log.created_on DESC
        LIMIT 1
    )

This looks expensive. Couldn't find any other way though.

user330315 · Accepted Answer · 2022-04-27 21:04:47Z

2

One way is to update both columns in a single expression:

UPDATE users u
SET (last_login, last_ip) = (
        SELECT created_on, ip
        FROM user_login_log log
        WHERE log.user_id = u.id
        ORDER BY log.created_on DESC
        LIMIT 1)

answered Apr 27, 2022 at 21:04

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update multiple columns on PostgreSQL with values from another table [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related