[SOLVED] PostgreSQL update statement with join for switching holder of relation of one to one

PostgreSQL update statement with join for switching holder of relation of one to one

There are two tables MAIN and SUB. Currently the sub table contains the relation, but there is the need to make MAIN to be the owner of the relation.

Here is example content of the two tables:

MAIN
| id | sub_id |
| 1  | null   |
| 2  | null   |
| 3  | null   |

SUB
| id | main_id |
| 1  | 1       |

Doing this script UPDATE main SET sub_id = sub.id FROM main m JOIN sub s ON s.main_id = m.id; does the follwing result for the MAIN table:

MAIN
| id | sub_id |
| 1  | 1      |
| 2  | 1      |
| 3  | 1      |

And this is the expected result that needs to be achieved

MAIN
| id | sub_id |
| 1  | 1      |
| 2  | null   |
| 3  | null   |

I have tryied also with onner, left, right, left outer and right outer joins, but always the same result

Solution

Your current query uses the table "main" twice and without a relation. This causes the update to change all records.

UPDATE main 
SET sub_id = sub.id 
FROM sub s 
WHERE s.main_id = main.id;

And you can't use the JOIN-syntax.