skip to Main Content

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..

3

Answers


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

    network_profile_name = 'James Bond',
    

    then try it should work

    Login or Signup to reply.
  2. 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
    
    
    Login or Signup to reply.
  3. 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;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search