Postgres UPDATE statement

Question

I have moved from mysql to psql, but find it hard to get my head around the UPDATE statement using multiple left joins.

How would you rewrite this in Postgres? (I am using postresql 9.4)

update task t 
    left join project p on t.project_id = p.id 
    left join client c on t.client_id = c.id 
    left join user u on t.user_id = u.id
set t.project_name = p.name, 
     t.client_name = c.name,
     t.user_name = u.name;

Any pointer will be welcome.

stackoverflow.com/search?q=[postgresql]+update+join and of course the examples in the manual — user330315
– user330315, Commented Apr 14, 2015 at 13:42
All the example only refer to one join. I need to do multiple joins. — sophia
– sophia, Commented Apr 14, 2015 at 14:10
There is no difference in the syntax between a join with one table and a join with multiple tables — user330315
– user330315, Commented Apr 14, 2015 at 14:13
You use only left joins, is it intentional - to update those rows in task table to null that have no project? With inner join to project it's something like this UPDATE task SET project_name = p.NAME ,client_name = c.NAME ,user_name = u.NAME FROM project p ON task.project_id = p.id LEFT JOIN client c ON task.client_id = c.id LEFT JOIN user u ON task.user_id = u.id;. To use a LEFT JOIN with project do a self-join to task or use subqueries in the SET part... — Simo Kivistö
– Simo Kivistö, Commented Apr 14, 2015 at 18:42

oᴉɹǝɥɔ · Accepted Answer · 2015-05-21 16:40:11Z

0

Here you go:

WITH task_data AS (
    SELECT t.id,
        p.name AS project_name,
        c.name AS client_name,
        u.name AS user_name
    FROM task t 
        LEFT JOIN project p ON t.project_id = p.id 
        LEFT JOIN client c ON t.client_id = c.id 
        LEFT JOIN "user" u ON t.user_id = u.id
)
UPDATE task t
FROM task_data d
SET
    project_name = d.project_name, 
    client_name = d.client_name,
    user_name = d.user_name
WHERE t.id = d.id

I would be curious to see if there is a more efficient way

answered May 21, 2015 at 16:40

oᴉɹǝɥɔ

2,1351 gold badge21 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

Postgres UPDATE statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related