Copy value from one row to another if column matches

  • Hi there,

    I just don't get it.

    Need to update rows in same table.

    The value usr_kataloghierarchie (see attached files) need to be copied to all rows when pm_extended_id matches and usr_kataloghierarchie is NULL.

    Would appreciate your help.

    Many thanks in advance.

    Bent

     

    Attachments:
    You must be logged in to view attached files.
  • It appears you're looking to join rows where p1.usr_kataloghierarchie is not null and p1.pm_extended_id equals the left 8 characters of p2.pm_extended_id.

    select p1.usr_kataloghierarchie, p2.pm_extended_id
    from pm_process p1
    join pm_process p2 on p1.pm_extended_id=left(p2.pm_extended_id, 8)
    where p1.usr_kataloghierarchie is not null;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Got a hint and could solve it with this statement:

    wth x as (select pm_extended_id, usr_kataloghierarchie from [dbo].[pm_process] where usr_kataloghierarchie is not null) update [dbo].[pm_process] set usr_kataloghierarchie = x.usr_kataloghierarchie from x where [dbo].[pm_process].pm_extended_id like x.pm_extended_id + '%' and [dbo].[pm_process].usr_kataloghierarchie is null;

    • This reply was modified 2 years, 10 months ago by  bent.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply