Equivalent of ON CONFLICT for UPDATE statement-CodePudding

I have a problem where I want to trim a column with preceding zeros. The problem is there is a unique constraint on that table for few columns and the column which I am trying to fix is one of them. Some data has been manipulated by the user which gave rise to these duplicate rows. I dont want to touch them and leave them as it is. But when I issue the following command, I am getting the unique constraint violation which does makes sense. But how to overcome it. I know for INSERT there is a ON CONFLICT DO NOTHING statement is there, but is there anything similar for UPDATE statement. When I issue the following command, I am getting the following, since po_number 400102194 has been already inserted by the user from Front end.

update moa.part set po_number = TRIM(LEADING '0' from po_number) where po_number like '0%'

ERROR:  duplicate key value violates unique constraint "part_4col_uni_idx"
DETAIL:  Key (part_number, esn_id, po_number, tenant_id)=(642W9874-501, 1709, 400102194, 1) already exists.
SQL state: 23505

So I want to try the following something similar, but I know this will not work

update moa.part set po_number = TRIM(LEADING '0' from po_number) where po_number like '0%' 
ON CONFLICT ON CONSTRAINT part_4col_uni_idx DO NOTHING;

How do I achieve the same effect

CodePudding user response：

Join the moa.part table to itself to find all the part numbers that have a leading zero and also do not have a leading zero, and exclude those with a leading zero from the rows that are updated. For example:

update moa.part
set po_number = trim(leading '0' from po_number)
where
    po_number like '0%'
    and po_number not in (
        select
            pa.po_number
        from
            moa.part as pa
            inner join moa.part as pb on trim(leading '0' from pa.po_number) = pb.po_number
                and pa.po_number <> pb.po_number
    ;

Though it seems as though it might be better to resolve those near-duplicate part numbers by merging or by renaming one of each pair.

CodePudding user response：

I am not aware of an equivalent for updates, but you can use a subquery with a EXISTS to check if the conversion conflicts with another unique po_number, e.g.

UPDATE part q1
SET po_number = TRIM(LEADING '0' FROM q1.po_number) 
WHERE 
  q1.po_number LIKE '0%' AND 
  NOT EXISTS (
    SELECT 1 FROM part q2 
    WHERE 
      TRIM(LEADING '0' FROM q2.po_number) = TRIM(LEADING '0' FROM q1.po_number) 
      AND q1.part_id <> q2.part_id);

Demo: db<>fiddle