February 25, 2015 at 1:12 am
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
February 25, 2015 at 2:20 am
try using CASE statement in UPDATE clause
February 25, 2015 at 3:19 am
I tried but it does't update
update Table1
set weight = case
when table1.state=d.state and table1.month=d.month and table1.year=d.year then d.weight
else Table1.weight
end,
stateweight = case
when table1.state=d.state and table1.month=d.month then d.stateweight
else Table1.stateweight
end,
overallweight = d.overallweight
from table2 d
where table1.year = d.year;
it only updates the overallweight column ,not other 2 column were updated... pls help me
February 25, 2015 at 3:24 am
I suspect that you will need to do this as three updates.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2015 at 3:54 am
tks for the replay,Its a huge table which has more than 50 Million data so i need in one update query is there any other option??
February 25, 2015 at 5:14 am
venkaat105 (2/25/2015)
tks for the replay,Its a huge table which has more than 50 Million data so i need in one update query is there any other option??
How many records in of the table are actually being updated?
Have you though about doing a select into a temp table, doing your manipulation on that data, then doing an update of the original using the temp table as the source?
Add the table's key value as part of the select into the temp table, then update the main table from the temp table with a join on the key value.
February 25, 2015 at 5:28 am
tks for ur response, We don't ,exactly it may be around 20 thousand for particular ID, And i have done it once using the temp table process it takes lot of time for the (joins and Update) ,that the reason i came to the above option ... it is quick but it updates only one column... So pls ?
February 25, 2015 at 7:29 am
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;
it works fine
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply