PostgreSQL complex update query

Question

I'm using PostgreSQL 12 and I must perform an update query to change some values from several table.

Now suppose I have two table, the first one represent a Domain table, for Example

CityDomain
id    bankid  code  description
1       1      AS   ......
2       1      BS   ......
3       1      CS   ......
4       2      AS   ......
5       2      BS   ......
6       2      CS   ......

Now I must erase the tuples with bankid equals to 1 but clearly I have other tables where this domain is represented. For Examples

File
id  city_domain_id  number  ....
1       1             108      
2       2             109
3       1             110
4       4             111

Therefore I have to replace all tuples of following query (to be more clear):

select * from File where city_domain_id in (SELECT id from CityDomain where bankid = 1)

with those connected to bankid = 2

To be more clear, to erase the first three tuples from CityDomain without ConstraintViolation the above table should become:

File
id  city_domain_id  number  ....
1       4             108   // because AS of bank 2 has id 4
2       5             109   // because BS of bank 2 has id 5
3       4             110   // because AS of bank 2 has id 4
4       4             111

The only way I've found to solve this is with a procedure like this:

DO
$$
declare dato record;
    dati cursor
    for select f.id, f.city_domain_id, c.code, c.bankid from file f, city_domain c where f.city_domain_id = c.id AND f.city_domain_id in (
        select c.id from city_domain c where c.bankid = 1);
begin
open dati;
loop
fetch dati into dato;
exit when not found;

update file set city_domain_id = sub.id from (
    select c.id from city_domain c where c.bankid = 2 AND c.code = dato.code) sub 
    WHERE file.id = dato.id; 

end loop;
close dati;
end; $$

There is a more simple way to do this? Maybe without a procedure?

Answer 1

This is your update outlook

select d1.code, d1.id old_domain_id, d2.id new_doamin_id
from CityDomain d1
join CityDomain d2
on d1.code = d2.code and d1.bankid = 1 and d2.bankid = 2
;

code|old_domain_id|new_doamin_id|
----+-------------+-------------+
AS  |            1|            4|
BS  |            2|            5|
CS  |            3|            6|

A plain PostgreSQL Update (using the abobe join) is enough

update File as f 
set city_domain_id = d2.id
from CityDomain d1
join CityDomain d2
on d1.code = d2.code and d1.bankid = 1 and d2.bankid = 2
where f.city_domain_id in 
(select id from CityDomain where bankid = 1) and
f.city_domain_id = d1.id;

Final result

select * from file order by id;

id|city_domain_id|number|
--+--------------+------+
 1|             4|   108|
 2|             5|   109|
 3|             4|   110|
 4|             6|   111|