CODEWITHSUNDEEP
sql-serverdatabase
Sathish A
Sathish A

Reputation: 29

How to Update multiple column with Multiple condition

I need to update multiple columns in a table with multiple condition.

For example, this is my Query

update Table1 
set weight= d.weight,
    stateweight=d.stateweight,
    overallweight=d.overallweight 
from
     (select * from table2)d 
      where table1.state=d.state and
          table1.month=d.month and 
          table1.year=d.year

If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column

NOTE: I can't write an update query for each condition separately because its a huge select

Table1

Year Month State Weight StateWeight Overweight
2014  1     AA    0.00    0.00        0.00
2014  3     AA    0.00    0.00        0.00
2014  1     AB    0.00    0.00        0.00
2014  2     AB    0.00    0.00        0.00
2014  3     AC    0.00    0.00        0.00
2014  1     DD    0.00    0.00        0.00

Table2 

Year Month State Weight StateWeight Overweight
2014  1     AA    2.20    5.00        2.22
2014  2     AA    1.70    5.00        2.22
2014  3     AA    8.30    5.00        2.22
2014  1     AB    5.80    2.11        2.22
2014  2     AB    7.40    2.11        2.22
2014  3     AB    9.10    2.11        2.22
2014  1     AC    3.42    5.14        2.22
2014  1     DD    8.88    9.00        2.22

My Result should be (My Updated Table1 should be )

Year Month State Weight StateWeight Overweight
2014  1     AA    2.20    5.00        2.22
2014  3     AA    8.30    5.00        2.22
2014  1     AB    5.80    2.11        2.22
2014  2     AB    7.40    2.11        2.22
2014  3     AC    0.00    0.00        2.22
2014  1     DD    0.00    9.00        2.22

but the query which u given were updated only Overweight Column not other 2 column(weight & StateWeight)

Upvotes: 0

Views: 80

Answers (1)

animateme
animateme

Reputation: 309

You can accomplish this by changing the criteria in your where clause, and adding case statements to the update.

Here is an example:

with
cte as  (   select      t1.Year,
                        t1.Month,
                        t1.State,
                        Weight      =   MAX(case
                                                when t1.State = t2.State and t1.Month = t2.Month
                                                then t2.Weight
                                                else t1.Weight
                                            end),
                        StateWeight =   MAX(case
                                            when t1.State = t2.State and t1.Month = t2.Month
                                            then t2.StateWeight
                                            else t1.StateWeight
                                        end),
                        Overweight  =   MAX(t2.Overweight)
            from        Table1  as  t1
            inner join  Table2  as  t2  on  t1.Year = t2.Year
            group by    t1.Year, t1.Month, t1.State)
update      t1
set         Weight  =   tv.Weight,
            StateWeight =   tv.StateWeight,
            Overweight = tv.Overweight
from        Table1  as  t1
inner join  cte     as  tv  on  t1.Year = tv.Year
                            and t1.Month = tv.Month
                            and t1.State = tv.State;

Upvotes: 1

Related Questions