Postgresql - use JOIN with UPDATE

Question

I want to UPDATE table row in member_network table using WHERE clause by team_id = 91 and using JOIN.

Main table member_network structure look like:

| id |  network_profile_name |                              
|----------------------------|
|  1 |    John Doe           |

I have two fields in two more connected tables with the values I need.

Table team_member_network structure looks like:

| id | team_member_id | member_network_id |
|----|----------------|-------------------|
|  2 |             1  |                 1 |

Table team_member:

| id    | team_id | member_id |
| ------|---------|-----------|
| 1     |      91 |   1679817 |

This is some kind of reverse relationship

My work so far:

   UPDATE member_network
        SET
            network_profile_name = 'James Bond'
        JOIN team_member_network
            ON member_network.id = team_member_network.member_network_id
        JOIN team_member
            ON team_member_network.team_member_id = team_member.id
        WHERE team_id = 91;

With an error:

Syntax error: 7 ERROR: syntax error at or near "JOIN\

Works on SELECT but how should I use JOIN when updating selected row? Related posts I found did not help in my case..

UPDATE member_network SET network_profile_name = 'James Bond' from member_network — Jens
– Jens, Commented Aug 17, 2022 at 11:35
Duplicate alias: 7 ERROR: table name \"member_network\" specified more than once @Jens — Filip Stojavonic
– Filip Stojavonic, Commented Aug 17, 2022 at 11:39
I am using team_id to update column in member_network table @a_horse_with_no_name — Filip Stojavonic
– Filip Stojavonic, Commented Aug 17, 2022 at 11:50

user330315 · Accepted Answer · 2022-08-17 11:54:12Z

2

I don't see why you would need JOIN for this:

UPDATE member_network mn
  SET network_profile_name = 'James Bond'
WHERE EXISTS (SELECT *
              FROM team_member_network tmn
                JOIN team_member tm ON tmn.team_member_id = tm.id
              WHERE mn.id = tmn.member_network_id
                AND tm.team_id = 91);

If you really want to "join" the tables, then you need to do that in the WHERE clause of the UPDATE statement. As documented in the manual you need a FROM clause first - but that should not repeat the target table.

UPDATE member_network mn
  SET network_profile_name = 'James Bond'
FROM team_member_network tmn
  JOIN team_member tm ON tmn.team_member_id = tm.id
WHERE mn.id = tmn.member_network_id --<< this is the "join" to the target table
  AND tm.team_id = 91;

answered Aug 17, 2022 at 11:54

user330315

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

1 Comment

Filip Stojavonic Over a year ago

Thanks a lot for the example. I am now more aware of how this should be handled. I used your example. @a_horse_with_no_name

Vasanth R · Accepted Answer · 2022-08-17 11:36:29Z

0

Just remove the comma (,) in the end of the line

network_profile_name = 'James Bond',

then try it should work

answered Aug 17, 2022 at 11:36

Vasanth R

2401 gold badge2 silver badges21 bronze badges

1 Comment

Filip Stojavonic Over a year ago

Thank you, I edited my post as I already fix that. But the issue is the same. @VasanthR

Lajos Arpad · Accepted Answer · 2022-08-17 11:59:01Z

0

It is:

update table1 t1
set t1.field = t2.'value'
from table2 t2
where t1.id = t2.t1_id

In your case:

   UPDATE member_network
        SET
            network_profile_name = 'James Bond'
        FROM team_member
        JOIN team_member
            ON team_member_network.team_member_id = team_member.id
        WHERE team_id = 91 and
            member_network.id = team_member_network.member_network_id

edited Aug 17, 2022 at 11:59

answered Aug 17, 2022 at 11:41

Lajos Arpad

80.4k42 gold badges122 silver badges234 bronze badges

4 Comments

Filip Stojavonic Over a year ago

Thanks for the answer but I get an error: Duplicate alias: 7 ERROR: table name \"member_network\" specified more than once @LajosArpad

user330315 Over a year ago

You should not repeat the target table of the update in the FROM clause

Filip Stojavonic Over a year ago

Without FROM it's Syntax error: 7 ERROR: syntax error at or near \"JOIN\ @a_horse_with_no_name

Lajos Arpad Over a year ago

@FilipStojavonic thanks for pointing that out, I sent the answer too quickly initially. I have edited now. Can you test it?

Collectives™ on Stack Overflow

Postgresql - use JOIN with UPDATE

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related