none
How to write a query for finding something from a set RRS feed

  • Question

  • I'm running SQL Server 2012, and want to solve the following problem:

    I have a Table A (tableA) like this:

    Column1 Column2
    1 1
    2 1
    3 1
    1 2
    2 2
    3 2
    2 3
    3 3
    2 4
    3 4

    And I have a second table (tableB) as this:

    Column1 Column2
    1 1
    2 2
    3 3
    4

    Now I want to fill in the blank value in Column2 in tableB. The value I want to fill in is the lowest value from Column2 in tableA where the value from Column1 in tableB matches the Column2 in tableA and finds the same set of values from tableA where the same values from Column1 in tableA is the only values for the group of Column2-values in tableA. Hard to explain easy...

    But in another phrasing: Since 4 has value 2 and 3 from Column1 in tableA I want to find the value 3 from Column2 in tableA because 3 is the smallest number where only value 2 and 3 are the full set of one value from Column2 in tableA. I don't want to find the value 1 or 2, because they have a group with value 1, 2 and 3 in column1.

    Can you help me write a SQL query for this?

    Hope you understand what I mean, or else tell me, and I will try to explain more deeply.

    Friday, October 18, 2019 7:43 PM

Answers

  • Check this experimental script:

    create table #TableA ( Column1 int, Column2 int ) 
    create table #TableB ( Column1 int, Column2 int ) 
    
    insert into #TableA values
    	( 1, 1 ),
    	( 2, 1 ),
    	( 3, 1 ),
    	( 1, 2 ),
    	( 2, 2 ),
    	( 3, 2 ),
    	( 2, 3 ),
    	( 3, 3 ),
    	( 2, 4 ),
    	( 3, 4 )
    
    insert into #TableB values
    	( 1, 1	  ),
    	( 2, 2	  ),
    	( 3, 3	  ),
    	( 4, NULL )
    
    
    select * from #TableA
    select * from #TableB
    
    ;
    with Q1 as 
    (
    	select a.*
    	from #TableA as a
    	inner join #TableB as b on a.Column2 = b.Column1
    	where b.Column2 IS NULL
    ),
    Q2 as 
    (
    	select * from #TableA
    	where Column1 not in (select Column1 from Q1)
    ),
    Q3 as
    (
    	select Q1.Column2 as Column1, MIN(a1.Column2) as Column2
    	from #TableA as a1
    	inner join Q1 on Q1.Column1 = a1.Column1
    	where a1.Column2 not in ( select Column2 from Q2 ) 
    	group by Q1.Column2
    )
    update #TableB
    set Column2 = Q3.Column2
    from Q3
    where #TableB.Column1 = Q3.Column1
    
    
    select * from #TableB
    

     

    By the way, which result should be returned if TableA contains just these rows:

           2  1

           3  3

           2  4

           3  4 ?

     

    • Marked as answer by runarlan Tuesday, October 22, 2019 9:45 PM
    Saturday, October 19, 2019 10:29 AM

All replies

  • Please post your expected result from sample tables in a tabular form to help to understand your question for a query. Thanks.
    Friday, October 18, 2019 7:58 PM
    Moderator
  • Hi Runarlan,

    Based on your example all columns have integer values. Is that the case in your data set or did you just use numbers to help make explaining it more clear?

    Am I safe in assuming you are wanting to update multiple rows that are missing Table B Column 2 values?

    If that's the case you may need to look into using a stored procedure to loop through Table B.

    Another option might be to use a case statement within your update.

    I'm not 100% on how to go about this either but am very interested in the answer as well. I will do some digging and see what I can come up with if you don't see a reply soon.

    Friday, October 18, 2019 8:07 PM
  • Hi Jingyang! Basically what I want to return is just the number "3".
    Friday, October 18, 2019 10:31 PM
  • Hi Christopher!

    There's almost just integers in my dataset. Except that column1 in tableA will be either two columns (one char and one integer) or one column as a char, concatenating those two columns. 

    Your assumption about updating multiple rows is true. There could be a bunch of rows with missing value. So a stored procedure could be a solution, I just can't get my head around figuring how to write the code for getting correct output. 

    Friday, October 18, 2019 10:36 PM
  • Check this experimental script:

    create table #TableA ( Column1 int, Column2 int ) 
    create table #TableB ( Column1 int, Column2 int ) 
    
    insert into #TableA values
    	( 1, 1 ),
    	( 2, 1 ),
    	( 3, 1 ),
    	( 1, 2 ),
    	( 2, 2 ),
    	( 3, 2 ),
    	( 2, 3 ),
    	( 3, 3 ),
    	( 2, 4 ),
    	( 3, 4 )
    
    insert into #TableB values
    	( 1, 1	  ),
    	( 2, 2	  ),
    	( 3, 3	  ),
    	( 4, NULL )
    
    
    select * from #TableA
    select * from #TableB
    
    ;
    with Q1 as 
    (
    	select a.*
    	from #TableA as a
    	inner join #TableB as b on a.Column2 = b.Column1
    	where b.Column2 IS NULL
    ),
    Q2 as 
    (
    	select * from #TableA
    	where Column1 not in (select Column1 from Q1)
    ),
    Q3 as
    (
    	select Q1.Column2 as Column1, MIN(a1.Column2) as Column2
    	from #TableA as a1
    	inner join Q1 on Q1.Column1 = a1.Column1
    	where a1.Column2 not in ( select Column2 from Q2 ) 
    	group by Q1.Column2
    )
    update #TableB
    set Column2 = Q3.Column2
    from Q3
    where #TableB.Column1 = Q3.Column1
    
    
    select * from #TableB
    

     

    By the way, which result should be returned if TableA contains just these rows:

           2  1

           3  3

           2  4

           3  4 ?

     

    • Marked as answer by runarlan Tuesday, October 22, 2019 9:45 PM
    Saturday, October 19, 2019 10:29 AM
  • This will update only the NULL values in Column2 for TableB with the lowest from Column1 in TableA where Column2 in TableA = Column1 in TableB.

    Declare @TableA Table (Column1 int, Column2 int); 
    Declare @TableB Table (Column1 int, Column2 int); 
    
     Insert Into @TableA 
     Values (1, 1), (2, 1), (3, 1), (1, 2), (2, 2), (3, 2), (2, 3), (3, 3), (2, 4), (3, 4);
    
     Insert Into @TableB
     Values (1, 1), (2, 2), (3, 3), (4, Null);
    
     Select *
       From @TableB tb;
    
     Update tb
        Set tb.Column2 = r.Column2
    -- Select *
       From @TableB                 tb
      Outer Apply (Select Top 1
                          ta.Column2
                     From @TableA   ta
                    Where ta.Column2 = tb.Column1
                    Order By
                          ta.Column1
                  )                  r
      Where tb.Column2 Is Null;
    
     Select *
       From @TableB tb;
    
    


    Jeff Williams

    Saturday, October 19, 2019 3:36 PM
  • Hello Viorel_

    Thank you so much! This seems to work just fine. 

    If TableA contains the row you specified, I want it to return 1 - as it does! 

    Thanks!

    Saturday, October 19, 2019 4:51 PM
  • Hi ,

    Thank you for your posting,

     

    Could you please share us your logic more clearly? If I have an incorrect understanding , please let me know.

     

    Also , you can use your simple data to explain .

    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.

    Monday, October 21, 2019 3:23 AM
  • Discovered that it didn't work as expected, and found out that I needed to add a while-loop to do one row at a time to handle a bit more complex situation than the one I previously explained:

    declare @ID bigint
    
    while (select count(*) from #TableB where Column2 is null) > 0
    BEGIN
    set @ID = (select top 1 Column1 from #TableB where Column2 is null);
    with Q1 as 
    (
    	select a.*
    	from #TableA as a
    	where Column2 = @ID
    ),
    Q2 as 
    (
    	select * from #TableA
    	where Column1 not in (select Column1 from Q1)
    ),
    Q3 as
    (
    	select Q1.Column2 as Column1, MIN(a1.Column2) as Column2
    	from #TableA as a1
    	inner join Q1 on Q1.Column1 = a1.Column1
    	where a1.Column2 not in ( select Column2 from Q2 ) 
    	group by Q1.Column2
    )
    update #TableB
    set Column2 = Q3.Column2
    from Q3
    where #TableB.Column1 = Q3.Column1
    
    END

    Here you can see a real example which this updated code-block could handle:

    

    As you can see, I have two values that I want to update.

    You can see from the yellow marked values, that for 333862 I would like to fill in the value 332513.

    And for the blue values, I would like to fill in the value 333537 for the null-value of 333863.

    Don't get confused from the previous values in Column2 #TableB. In the real example I use the returned value to search for it in #TableB Column1 and return Column2. 

    Thursday, October 24, 2019 12:03 PM
  • Hi Rachel_Wang,

    I'm sorry but I didn't quite understand your explanation. 

    You can see my recently posted example to maybe get a clearer view of the problem.

    What I want is to find the rows in #TableA that has the same value in Column2 as Column1 in #TableB. Then I want to see what values from Column1 I have, and find the lowest value in Column2 #TableA where the same set of values in Column1 is the only values having this value.

    Hope that maybe this was a better explanation.

    Thursday, October 24, 2019 12:08 PM
  • Discovered that it didn't work as expected, and found out that I needed to add a while-loop to do one row at a time to handle a bit more complex situation than the one I previously explained:

    declare @ID bigint
    
    while (select count(*) from #TableB where Column2 is null) > 0
    BEGIN
    set @ID = (select top 1 Column1 from #TableB where Column2 is null);
    with Q1 as 
    (
    	select a.*
    	from #TableA as a
    	where Column2 = @ID
    ),
    Q2 as 
    (
    	select * from #TableA
    	where Column1 not in (select Column1 from Q1)
    ),
    Q3 as
    (
    	select Q1.Column2 as Column1, MIN(a1.Column2) as Column2
    	from #TableA as a1
    	inner join Q1 on Q1.Column1 = a1.Column1
    	where a1.Column2 not in ( select Column2 from Q2 ) 
    	group by Q1.Column2
    )
    update #TableB
    set Column2 = Q3.Column2
    from Q3
    where #TableB.Column1 = Q3.Column1
    
    END

    Here you can see a real example which this updated code-block could handle:

    

    As you can see, I have two values that I want to update.

    You can see from the yellow marked values, that for 333862 I would like to fill in the value 332513.

    And for the blue values, I would like to fill in the value 333537 for the null-value of 333863.

    Don't get confused from the previous values in Column2 #TableB. In the real example I use the returned value to search for it in #TableB Column1 and return Column2. 

    create table tableA (Column1 int,Column2 int)
    
    insert into tableA values(22366,33848)
    ,(23472,33848)
    ,(6809,33848)
    ,(7985,33848)
    ,(12762,33848)
    
    ,(22566,121803)
    ,(23472,121803)
    ,(6809,121803)
    ,(7985,121803)
    ,(12762,121803)
    
    ,(22566,225520)
    ,(23472,225520)
    ,(6809,225520)
    ,(7985,225520)
    ,(12762,225520)
    
    ,(22566,276435)
    ,(23472,276435)
    ,(6809,276435)
    ,(7985,276435)
    ,(12762,276435)
    
    ,(22566,319362)
    ,(23472,319362)
    ,(6809,319362)
    ,(7985,319362)
    ,(12762,319362)
    
    ,(22566,332513)
    ,(6809,332513)
    ,(7985,332513)
    
    ,(23472,333537)
    ,(12762,333537)
    
    ,(22566,333570)
    ,(6809,333570)
    ,(6809,333570)
    ,(7985,333570)
    ,(7985,333570)
    
    ,(23472,333861)
    ,(12762,333861)
    ,(12762,333861)
    
    ,(22566,333862)
    ,(6809,333862)
    ,(7985,333862)
    
    ,(23472,333863)
    ,(12762,333863)
    
    create table tableB (Column1 int,Column2 int)
    
    insert into tableB values
    (33848,33848)
    ,(121803,33848)
    ,(225520,33848)
    ,(276435,33848)
    ,(319362,33848)
    ,(332513,33848)
    
    ,(333537,333537)
    ,(333570,33848)
    ,(333861,333537)
    
    ,(333862,null)
    ,(333863,null)
    
    
    ---SQL Server 2017
    ;with mycte as (
    select Column2
    ,string_agg(column1,';') WITHIN GROUP ( ORDER BY column1 ASC) 
    aggcol
    from tableA
    Group by Column2
    )
    
    ,mycte2 as (
    select t.Column1,m2.Column2 , row_number() Over(partition by m1.aggcol Order by m1.Column2) rn
    from mycte m1 join 
    mycte m2 on m1.aggcol=m2.aggcol 
    join tableB t on t.column1= m1.Column2 and t.column2 is null
     )
     Merge tableB tgt
     using mycte2 src on tgt.Column1=src.Column1
     WHen matched and rn=1 then 
     Update
     Set Column2=src.column2;
    
     
    
    
     select Column1,Column2 from tableB
     
     
    
     
     
    
    drop table tableA,tableB 
    


    Thursday, October 24, 2019 4:04 PM
    Moderator
  • ;with mycte as (
    select t1.Column2,
     
           Stuff(( SELECT ';' + Cast(t2.column1 as varchar(10))
               FROM tableA t2
              WHERE t2.Column2 = t1.Column2 
    		  ORDER BY column1
                FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS aggcol
      FROM tableA t1  
    Group by t1.Column2
    )
    
    ,mycte2 as (
    select t.Column1,m2.Column2 , row_number() Over(partition by m1.aggcol Order by m1.Column2) rn
    from mycte m1 join 
    mycte m2 on m1.aggcol=m2.aggcol 
    join tableB t on t.column1= m1.Column2 and t.column2 is null
     )
     Merge tableB tgt
     using mycte2 src on tgt.Column1=src.Column1
     WHen matched and rn=1 then 
     Update
     Set Column2=src.column2;
      
    
     select Column1,Column2 from tableB

    Thursday, October 24, 2019 4:11 PM
    Moderator