PostgreSQL UPDATE JOIN ... How does it work?

Question

I'm having trouble understanding UPDATE with some sort of JOIN in PostgreSQL

I have the following table (names), sometimes a synonym is filled in the 3rd column:

id,name,synm,sd
41,xgvf
24,y4tg
32,zagr,xgvf
48,argv,bvre
53,bvre

I like to fill column 4 (sd) with the 'parent' id (sd column is empty now)

id,name,synm,sd
41,xgvf
24,y4tg
32,zagr,xgvf,41
48,argv,bvre,53
53,bvre

I tried the following sql statement (and many similar version of it) ...

update names
set sd =
(select n2.id from names n1
   inner join names n2 
   on 
   n1.synm = n2.name);

... i get the following error:

ERROR:  more than one row returned by a subquery used as an expression
SQL state: 21000

I understand my current wrong SQL tries to fill one sd row with all found id's. So somehow I do not get it.

How do I fill the synonym id (sd) in the whole table? Perhaps WITH RECURSIVE like statements?

Answer 1

You can simulate the join like this:

update names n1
set sd = n2.id
from names n2
where n2.name = n1.synm;

See the demo.
Results:

| id  | name | synm | sd  |
| --- | ---- | ---- | --- |
| 41  | xgvf |      |     |
| 24  | y4tg |      |     |
| 53  | bvre |      |     |
| 48  | argv | bvre | 53  |
| 32  | zagr | xgvf | 41  |