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..
CodePudding user response:
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;
CodePudding user response:
Just remove the comma (,) in the end of the line
network_profile_name = 'James Bond',
then try it should work
CodePudding user response:
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