CODEWITHSUNDEEP
sql-server
user1787157
user1787157

Reputation: 1

How can I update by one UPDATE statement in SQL server for the following scenario?

How can I update by one UPDATE statement for the following scenario? I have 2 tables. The TableA contains the following fields: TestNumber, CO, CO2, N20, CH4 and the value of TestNumber column is 1, 2, 3

TableA

TestNumber      CO      CO2     N2O     CH4  
 1                                                              
 2                                                              
 3

The second table named TableB contains the following fields: TestNumber, TestIdentifier, TestValue and the values are the below:

TableB

TestNumber      TestIdentifier  TestValue      
 1                 CO           12.3   
 1                 CO2          10     
 2                 CO           9.8    
 2                 N20          45     
 3                 CO           12.5   
 3                 CO2          13.0

I’M EXPECTING THE RESULT AS:

TestNumber      CO      CO2     N2O     CH4   
 1              12.3    10                                             
 2              9.8             45                                     
 3              12.5    13.0

I did it using a chunk of code like, declare table variable. Then store data from TableB to the table variable. Then SET rowCount =1 then looping and deleting one row and update the TableA which work perfect. I got my expected result. But I believe I can do direct UPDATE command without too many table variables declaration and looping. I’m updating though but not directly.

I want to learn how I can update directly without looping.

Reply to:

Upvotes: 0

Views: 98

Answers (2)

praveen
praveen

Reputation: 12271

Try this :

You can use PIVOT to convert the TableB in the same form as TableA having TestIdentifier as the column names for the TableB

Select TestNumber ,[Co],[CO2],[N2O],[CH4]
FROM
   ( Select TestNumber,TestIdentifier,TestValue from TableB) as sourceTable
pivot 
   (
    sum(TestValue) 
    for TestIdentifier in ([Co],[CO2],[N2O],[CH4])
   )
as pvt

Now you can simply use inner join on TestNumber and update the TableA

Update t
set t.Co=cte.CO,
t.CO2=cte.CO2,
t.N2O=cte.N2O,
t.CH4=cte.CH4
from TableA as t
inner join cte 
on cte.TestNumber=t.TestNumber

The complete sql query is 

;With cte (TestNumber ,[Co],[CO2],[N2O],[CH4])
 as
 (
  Select TestNumber ,[Co],[CO2],[N2O],[CH4]
  FROM
      ( Select TestNumber,TestIdentifier,TestValue from TableB) as sourceTable
  pivot 
      (
       sum(TestValue) 
       for TestIdentifier in ([Co],[CO2],[N2O],[CH4])
      )
  as pvt
 )
  Update t
  set t.Co=cte.CO,
  t.CO2=cte.CO2,
  t.N2O=cte.N2O,
  t.CH4=cte.CH4
  from TableA as t
  inner join cte 
  on cte.TestNumber=t.TestNumber

For complete solution try SQLFIDDLE

Upvotes: 1

fabricio
fabricio

Reputation: 1393

You could read something about pivot tables... maybe it helps. But if your first table is static.. I mean, it's always the same coloumns and the same rows, you could do something like this:

Update tableA set
CO = (Select sum(testValue) from TableB where testNumber = 1 and testIdentifier = 'CO'),
CO2 = (Select sum(testValue) from TableB where testNumber = 1 and testIdentifier = 'CO2'),
N20 = (Select sum(testValue) from TableB where testNumber = 1 and testIdentifier = 'N20'),
CH4 = (Select sum(testValue) from TableB where testNumber = 1 and testIdentifier = 'CH4')
where testNumber = 1

You can write this two more times for the other tests... or if they are variable.. you can write this inside a while loop

Upvotes: 0

Related Questions