none
How to comapre value from same column and get the result from another column? RRS feed

  • Question

  • Hi All,

    I am looking for some help in comparison values from same column A and if match then insert value from column B.

    Please see explanation below;

    I have TABLE A:

    Column1-ID      Column 2-City    Column3-State

    1                         Bothel                NULL

    2                         BOTHEL              WA

    Now I would like to have result like this in destination Table B:

    Column1-ID      Column 2-City    Column3-State

    1                         Bothel                WA

    2                         BOTHEL              WA

    I want to compare values (Rows) from same column and if match then replace NULL value with value that other row has. 

    Any help would be appreciated!

    Thanks

    Wednesday, December 11, 2019 3:54 AM

Answers

  • CREATE TABLE mytable(
       [Column1-ID]       INT  NOT NULL 
      ,[Column2-City]   VARCHAR(100)   NULL
      ,[Column3-State] VARCHAR(100)   NULL
    );
    INSERT INTO mytable( [Column1-ID],[Column2-City],[Column3-State]) 
    VALUES (1,'Bothel',NULL),(2,'BOTHEL','WA')
    
    ;with mycte as (
    
    SELECT [Column1-ID],[Column2-City],[Column3-State], 
    max([Column3-State]) Over(partition by [Column2-City]) maxState
           
      FROM mytable )
    
      Update mycte
      Set [Column3-State]=maxState;
    
    
      select [Column1-ID],[Column2-City],[Column3-State] 
      from mytable
    
    
    drop table mytable

    • Marked as answer by AmitBDesai Thursday, December 12, 2019 7:29 PM
    Wednesday, December 11, 2019 4:24 AM
    Moderator

All replies

  • Hi,

    One more thing I would like add here for more clarificationl

    I want to compare Bothel = BOTHEL from above example and if they match then Null value in front of Bothel will replace by WA from id= 2.

    Eventually, I would like to write store procedure and will call that store procedure in SSIS.

    THanks

    Wednesday, December 11, 2019 4:00 AM
  • CREATE TABLE mytable(
       [Column1-ID]       INT  NOT NULL 
      ,[Column2-City]   VARCHAR(100)   NULL
      ,[Column3-State] VARCHAR(100)   NULL
    );
    INSERT INTO mytable( [Column1-ID],[Column2-City],[Column3-State]) 
    VALUES (1,'Bothel',NULL),(2,'BOTHEL','WA')
    
    ;with mycte as (
    
    SELECT [Column1-ID],[Column2-City],[Column3-State], 
    max([Column3-State]) Over(partition by [Column2-City]) maxState
           
      FROM mytable )
    
      Update mycte
      Set [Column3-State]=maxState;
    
    
      select [Column1-ID],[Column2-City],[Column3-State] 
      from mytable
    
    
    drop table mytable

    • Marked as answer by AmitBDesai Thursday, December 12, 2019 7:29 PM
    Wednesday, December 11, 2019 4:24 AM
    Moderator
  • Hi,

    create table #tableA (Column1_ID  int,    Column2_City  varchar(200),  Column3_State varchar(10))
    insert #tableA values (1,'Bothel',NULL), (2,'BOTHEL','WA')

    select a.Column1_ID, a.Column2_City, isnull(a.Column3_State,t1.Column3_State)
    from #tableA a,
    (select distinct Column2_City, Column3_State from #tableA where Column3_State is not null) t1
    where a.Column2_City = t1.Column2_City

    Mark as answer if it works. Thanks.

    Wednesday, December 11, 2019 4:32 AM
  • Hi ,

    Also, Please check following script.

    drop table mytable
    CREATE TABLE mytable(
       [Column1-ID]       INT  NOT NULL 
      ,[Column2-City]   VARCHAR(100)   NULL
      ,[Column3-State] VARCHAR(100)   NULL
    );
    INSERT INTO mytable( [Column1-ID],[Column2-City],[Column3-State]) 
    VALUES (1,'Bothel',NULL),(2,'BOTHEL','WA')
    
    update a set a.[Column3-State]= (case when a.[Column3-State] is null then b.[Column3-State] else a.[Column3-State] end )
    from mytable a 
    join mytable b 
    on upper(a.[Column2-City])=upper(b.[Column2-City]) and a.[Column1-ID]<>b.[Column1-ID]
    select * from mytable 
    /*
    Column1-ID  Column2-City    Column3-State
    ----------- --------------- -----------------
    1           Bothel          WA
    2           BOTHEL          WA
    
    */

    Best Regards,

    Rachel 


    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.

    Thursday, December 12, 2019 6:33 AM
  • Thank you Li for your response. it did work.
    Thursday, December 12, 2019 7:29 PM
  • Thanks Soumen appreciate your response.
    Thursday, December 12, 2019 7:30 PM
  • Thanks Rachel appreciate your response.
    Thursday, December 12, 2019 7:30 PM