updating records with loop in postgres-CodePudding

I have two tables.

organisation(
    id int4, 
    full_name varchar(512), 
    name varchar(255), 
    short_name varchar(255), 
    activity_id int4
)

contract(
    id int4, 
    registry_number varchar(30), 
    provider_id 
)

Table organisation have duplicate records of organisations which relate with contract table (organisation ID specified in the provider_id field in the contract table). I need to change the binding of contracts from duplicate organisations to the original organisations.

I can find all duplicates in organisation table(dupl_org_id)

select * from organisation o 
where activity_id is null and name is null and short_name = ''

after that for every record of duplicate organisations I need to find id of origin organisation(origin_org_id)

select id from organisation o 
where lower (full_name) = lower (<full_name current iteration with dupl_org_id>) and name is not null 
order by id asc

The full_name field is the same, but it is in different cases.

Then in the contract table, in the records that are linked to the current dupl_org_id, put the origin_org_id in the provider_id field:

update contract set provider_id = origin_org_id
where provider_id = dubl_org_id

My question - how to do these steps for every duplicate records. I guess I need to loop but don't know how it would be in postgresql.

CodePudding user response：

UPDATE contract
set provider_id = orig.id
from organisation dup
inner join organisation orig on lower(dup.full_name) = lower(orig.full_name)
where 
contract.provider_id = dup.id
and dup.activity_id is null
and dup.name is null
and dup.short_name = ''
and orig.name is not null