Updating A Table To Create Unique Ids In From A Substring In Postgresql

I have table1 with following columns and example of data: id, condition1, condition2, condition3, target_id 1, Westminster, Abbey Road, NW1 1FS, null 2, Westminster, Abbey Road, NW

Solution 1:

Your question suggests that you want a query like this:

update table1 t1
    set target_id = (select "unique id"
                     from table1 tt1
                     where tt1.condition1 = t1.condition1 and
                           tt1.condition2 = t1.condition2 andleft(tt1.condition3, 5) =left(t1.condition3, 5)
                    );

However, this will likely return an error of the sort "subquery returns more than one row". To fix that, you need a limit 1 or aggregation function. Something like:

update table1 t1
    set target_id = (selectmax("unique id")
                     from table1 tt1
                     where tt1.condition1 = t1.condition1 and
                           tt1.condition2 = t1.condition2 andleft(tt1.condition3, 5) =left(t1.condition3, 5)
                    );

EDIT:

If you just want to enumerate things, you can use dense_rank():

update table1 t1
    set target_id = tt1.seqnum
    from (select t1.*,
                 dense_rank() over (orderby condition1, condition2, left(condition3, 5)) as seqnum
          from table1 t1
         ) tt1
    where tt1.id = t1.id;