CODEWITHSUNDEEP
sqlsql-server
Sleepyfalcon
Sleepyfalcon

Reputation: 35

SQL - Updating rows with individual rows from another table

Trying to insert data to each appropriate row with matching ID's and DayNumber's from Table1 called #Test to Table2 called #BlueTable

I'm working with this

CREATE TABLE #Test (
            ID int,
            DayNumber  nvarchar(1), 
            MNumber nvarchar(2),
            QLetter nvarchar(1),
            Comments    nvarchar(60)
        )

insert into #Test(ID,DayNumber,MNumber,QLetter,Comments) 
  values(55,'1','2','A','000000'),
    (55,'5','2','A','111111'),
    (66,'7','2','A','222222'),
    (66,'7','2','B','333333')

  CREATE TABLE #BlueTable (
            ID nvarchar(40),
            DayNumber  nvarchar(1),
            BL_MN   nvarchar(2),
            BL_QL   nvarchar(1),
            BL_CM   nvarchar(60),
            BL_MN2  nvarchar(2),
            BL_QL2  nvarchar(1),
            BL_CM2  nvarchar(60)
        )

 declare @i int=1

while (@i <=7)
begin
   insert into #BlueTable(ID,DayNumber) 
  values(55,@i),
     (66,@i)

   set @i=@i+1
end

declare @loop int=1,
    @loopWord nvarchar(MAX)=''

   while(@loop<=2)-- HOW UPDATE IS RAN
  begin
       exec('update #BlueTable set BL_MN'+@loopWord+'=#Test.MNumber,BL_QL'+@loopWord+'=#Test.QLetter,BL_CM'+@loopWord+'=#Test.Comments from #Test
 where #BlueTable.ID=#Test.ID and #BlueTable.DayNumber = #Test.DayNumber')

    set @loop =@loop+1
    set @loopWord=@loop
 end


select * from #Test
select * from #BlueTable order by ID
drop table #Test
drop table #BlueTable

I was expecting at least some null values but it seems to overwrite previous updates and fills in with new data. This was how I Updated it

The output i've been trying to get should look like this when using (select * from #BlueTable where BL_CM is not null order by ID): What I want

ID  | DayNumber | BL_MN | BL_QL |    BL_CM     | BL_MN2 | BL_QL2 | BL_CM2 |
55       1          2       A       000000        null     null     null
55       5          2       A       111111        null     null     null
66       7          2       A       222222         2        B      333333

Instead I get something that looks like this: What I am getting

 ID  | DayNumber | BL_MN | BL_QL |    BL_CM     | BL_MN2 | BL_QL2 | BL_CM2 |
 55      1          2        A        000000        2         A     000000
 55      5          2        A        111111        2         A     111111
 66      7          2        A        222222        2         A     222222

Please help :(

Upvotes: 0

Views: 71

Answers (3)

uzi
uzi

Reputation: 4146

Have a look at this query. I suggest you to get your desired result first. Then insert in second table.

insert into #BlueTable
select
    ID, DayNumber, max(case when rn = 1 then MNumber end)
    , max(case when rn = 1 then QLetter end)
    , max(case when rn = 1 then Comments end)
    , max(case when rn = 2 then MNumber end)
    , max(case when rn = 2 then QLetter end)
    , max(case when rn = 2 then Comments end)
from (
    select
        *, rn = row_number() over (partition by ID, DayNumber order by MNumber, QLetter)
    from 
        #Test
) t
group by ID, DayNumber

Upvotes: 1

Bukhbayar
Bukhbayar

Reputation: 31

In my view, You will never get this result because when you insert the records into #Test table, you duplicated ID=66, DayNumber=7.

ID  | DayNumber | BL_MN | BL_QL |    BL_CM     | BL_MN2 | BL_QL2 | BL_CM2 |
55      1          2        A        000000        2         A     000000
55      5          2        A        111111        2         A     111111
66      7          2        A        222222        2         A     222222

So, probably, the result may looks like this. If you do not want to see this result, you could filter by QLetter ='A' only.

ID  | DayNumber | BL_MN | BL_QL |    BL_CM     | BL_MN2 | BL_QL2 | BL_CM2 |
55      1          2        A        000000        2         A     000000
55      5          2        A        111111        2         A     111111
66      7          2        A        222222        2         A     222222
66      7          2        B        333333        2         B     333333

So, I would like to recommend to use this query just after populate data and before do any update. For this update, you actually no need SQL Procedure. Just convert this SELECT query into update. Hope, this will helps.

    SELECT 
       B.ID, 
       B.DayNumber, 
       T.MNumber AS BL_MN_1, 
       T.QLetter AS BL_QL_1, 
       T.Comments AS BL_CM_1,
       T.MNumber AS BL_MN_2, 
       T.QLetter AS BL_QL_2, 
       T.Comments AS BL_CM_2
    FROM #BlueTable AS B
    INNER JOIN #Test T ON T.ID = B.ID AND T.DayNumber = B.DayNumber

OR

    SELECT 
       B.ID, 
       B.DayNumber, 
       T.MNumber AS BL_MN_1, 
       T.QLetter AS BL_QL_1, 
       T.Comments AS BL_CM_1,
       T.MNumber AS BL_MN_2, 
       T.QLetter AS BL_QL_2, 
       T.Comments AS BL_CM_2
    FROM #BlueTable AS B
    INNER JOIN #Test T ON T.ID = B.ID AND T.DayNumber = B.DayNumber AND T.QLetter = 'A'

Actually, I am wondering, why are you trying to update #BlueTable with same informations. For this case, it would work. If you tell me specific requirement, that could change this solution as well. Good luck.

Upvotes: 0

Vidmantas Blazevicius
Vidmantas Blazevicius

Reputation: 4802

A simple INNER JOIN update should work for your scenario. An example of how to update columns from table to another below.

UPDATE T1
SET T1.BL_CM2 = T2.BL_CM2, T1.BL_QL2 = T2.BL_QL2, ... more columns
FROM #Test T1
INNER JOIN #BlueTable T2
ON T1.ID = T2.ID AND T1.DAYNUMBER = T2.DAYNUMBER

EDIT:

I think what you are looking is something like below:

UPDATE T1
SET T1.BL_MN = T2.MNumber, T1.BL_QL = T2.QLetter, T1.BL_CM = T2.Comments, 
    T1.BL_MN2 = T3.MNumber, T1.BL_QL2 = T3.QLetter, T1.BL_CM2 = T3.Comments
FROM #BlueTable T1
LEFT JOIN #Test T2
ON T1.ID = T2.ID AND T1.DAYNUMBER = T2.DAYNUMBER AND T2.QLetter = 'A'
LEFT JOIN #Test T3
ON T1.ID = T3.ID AND T1.DAYNUMBER = T3.DAYNUMBER AND T3.QLetter = 'B'

Upvotes: 0

Related Questions