Composite update statement in SQL

Question

I'm not sure how to do this --

I've got a local table with fields as such that require updating.

select column_835 1,
    column_836 = 6,
    column_837 = 8,
    column_838 = 1,
    column_839 = 6,
    column_840 = 3,
    column_841 = 6,
    column_842 = 8
from #esp

the updating statement reads like this --

update  a
set     column_835 = b.percent
from    #esp a
join    #local_data b on a.c_no = b.c and a.fyear = b.fyear
where   b.color = 'blue'

what happens is i want to update row 'column_835' with the value in my local table where the color is blue.

the second query would update 'column_837'with the value for percent but that corresponds to green and so on. what i would love to do is to some how make one update statement instead of 8 or more. (if more colors get added we have to make more updates in the code).

How do I say update column_ from our local table with the

so for example 
column_835 = percent (but only where b.color = blue)
column_837 = percent (but only where b.color = green)
column_842 = percent (but only where b.color = white)
and so on.

data comes from below -- I've also got a second table with data like this

select  * 
from T_VALUES v
where v.column_id = 728

the data from table 'values' for column_id 728 returns as follows

id  keyword_no  key_value
840 728         red
839 728         white
837 728         green
835 728         blue
841 728         yellow
838 728         black
842 728         white
836 728         purple

@Cool_Br33ze I am writing a procedure (that is what the update statement is in) as well as the majority of the code. But the columns have been predetermined and cant be changed. I'm just not sure how to write one update statement that will smartly update all the rows instead of 8 update statements. — Elizabeth, Jan 10 '18 at 17:22

score 2 · Accepted Answer · answered Jan 10 '18 at 17:30

2

You could use case;

update  a
set     
column_835 = (CASE WHEN b.color='blue' THEN (b.percent) Else column_835 End),
column_837 = (CASE WHEN b.color='green' THEN (b.percent) Else column_837 End),
column_842 = (CASE WHEN b.color='white' THEN (b.percent) Else column_842 End)
from    #esp a
join    #local_data b on a.c_no = b.c and a.fyear = b.fyear

answered Jan 10 '18 at 17:30

lucky

12,734
4
24
46

this looks great, not sure why couldn't think of a case statement. implementing now. – Elizabeth Jan 10 '18 at 17:37
my result set has 5 lines where the join matches out but column_835 is only updated in one of those lines. I need it to update every line. I'm not sure why its not working – Elizabeth Jan 10 '18 at 17:45
you should remove where clause for 'blue'. – lucky Jan 10 '18 at 17:45
what do you mean. take out the WHEN portion? sorry for the confusion – Elizabeth Jan 10 '18 at 17:47
I mean, your original query has "where b.color = 'blue'". Did you remove it ? – lucky Jan 10 '18 at 17:47
i see whats happening, to pull data from column b we need to join on it ... but the join is messing up the rows to be updated. i'm looking into fixing this – Elizabeth Jan 10 '18 at 17:52
I have just tested it. There is no problem with query. – lucky Jan 10 '18 at 18:01
its multiplying the data (5 rows in local_Data column) and lets say 3 rows in esp column but in the join we see 15 rows. additionally of the 3 esp rows only 2 get updated ... – Elizabeth Jan 10 '18 at 18:20
How do you see 15 rows by applying inner join with 5 rows local_Data and 3 rows in esp. It is not possible. There would be different case here. – lucky Jan 10 '18 at 19:11
i sorted it out -- but it was multiplying the 3 rows times the 5 rows – Elizabeth Jan 10 '18 at 19:13

score 1 · Answer 2 · answered Jan 10 '18 at 19:12

update  a
set     column_835 = (select b.percent from #local_data b where color ='blue' and b.fyear = a.fyear and b.c = a.c_no),
        column__837 = (select b.percent  from #local_data b where color= 'green' and b.fyear = a.fyear and b.c = a.c_no),
        column_y_842 = (select b.percent  from #local_data b where color= 'white' and b.fyear = a.fyear and b.c = a.c_no)
from    #esp a

score 0 · Answer 3 · answered Jan 10 '18 at 17:36

if @Colour is an input parameter then try following

    DECLARE @id NVARCHAR(10) 
    SELECT
        @id = CAST(Id AS NVARCHAR(10))
    FROM
        T_VALUES v
    WHERE
        v.column_id = 728
    AND
        key_value = @Colour

--Dynamic SQL part
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 
'
    update  a
    set     column_' + @id + ' = b.percent
    from    #esp a
    join    #local_data b on a.c_no = b.c and a.fyear = b.fyear
    where   b.color = ''' + @Colour + '''
' ;
--PRINT @SQL
EXEC sys.sp_executesql @SQL ;

Composite update statement in SQL

3 Answers3