none
Tricky Update in the same table RRS feed

  • Question

  • I have a query that yield a result like this

    col1ID      col2      Name1      col3ID        col4       Name2      

    1             200          A             1               null         null

    2             null          null           2               300         A

    1             400          B              1               null          null

    2             null          null           2               500         B

    There is no unique ID for this table, say Extbl, colIDID and col3ID has the same value, either col2 or col3 has null value, also Name1 and Name2 has null value, how can I update Extbl to have result like this?

    col1ID      col2      Name1      col3ID        col4       Name2      

    1             200          A           1               200         A

    2             300          A           2               300         A

    1             400          B           1               400         B

    2             500          B           2               500         B

    Extbl has 6 columns where col col1ID and col col3ID have same value, either col col2 and col Name1 both have null, or col col4 and col Name2 both have null, then the rule is to update col2 = col4 if col2 is null, and col Name1 = col Name2 if col Name1 is null. Similarly, col4 = col2 if col4 is null, col Name2 = col Name1 if col Name2 is null, also col1ID must match col3ID when update. But it is tricky when row 1 and row 3 have same ID = 1. I'm stuck when do normal update. Is there a way to do that? 

    Thank you

    Kay_D




    • Edited by Kay_D Thursday, November 1, 2018 1:19 AM
    Wednesday, October 31, 2018 11:26 PM

Answers

  • Hi Kay_D,

    If you table is sql type, you could try to use query like below to achieve your goal

    select COL1ID, COL3ID, case when col2 IS NULL then col4 else col2 end as col2, case when Name1 IS NULL then Name2 else Name1 end as Name1,
    case when col4 IS NULL then col2 else col4 end as col4, case when Name2  IS NULL then Name1 else Name2 end as Name2
    
     from rr

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Kay_D Thursday, November 1, 2018 1:31 PM
    Thursday, November 1, 2018 3:10 AM
    Moderator
  • update Extbl
    set col2 = case when col2 is null then e.col4 else col2 end,
    Name1 = case when Name1 is null then e.Name2 else Name1 end,
    col4 = case when col4 is null then e.col2 else col4 end,
    Name2 = case when Name2 is null then e.Name1 else Name2 end
    from Extbl e 

    Cheers,

    • Marked as answer by Kay_D Thursday, November 1, 2018 1:31 PM
    Thursday, November 1, 2018 3:17 AM

All replies

  • Hi Kay_D,

    If you table is sql type, you could try to use query like below to achieve your goal

    select COL1ID, COL3ID, case when col2 IS NULL then col4 else col2 end as col2, case when Name1 IS NULL then Name2 else Name1 end as Name1,
    case when col4 IS NULL then col2 else col4 end as col4, case when Name2  IS NULL then Name1 else Name2 end as Name2
    
     from rr

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Kay_D Thursday, November 1, 2018 1:31 PM
    Thursday, November 1, 2018 3:10 AM
    Moderator
  • update Extbl
    set col2 = case when col2 is null then e.col4 else col2 end,
    Name1 = case when Name1 is null then e.Name2 else Name1 end,
    col4 = case when col4 is null then e.col2 else col4 end,
    Name2 = case when Name2 is null then e.Name1 else Name2 end
    from Extbl e 

    Cheers,

    • Marked as answer by Kay_D Thursday, November 1, 2018 1:31 PM
    Thursday, November 1, 2018 3:17 AM
  • Thanks Zoe. It works perfectly
    Thursday, November 1, 2018 1:31 PM