How to update multiple columns on PostgreSQL with values from another table-CodePudding

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.

CodePudding user response：

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)