sql server - While 'CASE WHEN' update, other columns become null -


i using user defined table type in tmy database update several columns via datatable.

so, wrote following stored procedure , it's working good. while updating respective columns, make other columns null.

my stored procudure:

alter procedure [dbo].[updateabcditemsbyvendorprice]       @tblitemupdate abcditemsupdate readonly begin     set nocount on;      merge abcditems abcdtable     using @tblitemupdate temptable     on abcdtable.itempartnumber = temptable.itempartnumber     when matched     update set         abcdtable.inpostprice = case when temptable.vendorname = 'inpost' temptable.price end,         abcdtable.inpostupdateddate = case when temptable.vendorname = 'inpost' getutcdate() end,         abcdtable.inpostabsoluteitem = case when temptable.vendorname = 'inpost' 0 end,          abcdtable.hrcprice = case when temptable.vendorname = 'hrc' temptable.price end,         abcdtable.hrcupdateddate = case when temptable.vendorname = 'hrc' getutcdate() end,         abcdtable.hrcabsoluteitem = case when temptable.vendorname = 'hrc' 0 end,          abcdtable.annecyprice = case when temptable.vendorname = 'annecy' temptable.price end,         abcdtable.annecyupdateddate = case when temptable.vendorname = 'annecy' getutcdate() end,         abcdtable.annecyabsoluteitem = case when temptable.vendorname = 'annecy' 0 end,          abcdtable.sydneyprice = case when temptable.vendorname = 'sydney' temptable.price end,         abcdtable.sydneyupdateddate = case when temptable.vendorname = 'sydney' getutcdate() end,         abcdtable.sydneyabsoluteitem = case when temptable.vendorname = 'sydney' 0 end; end

my table before executing query 'inpost':

inpostprice    hrcprice    ... 0              0           ...

my table after executing query 'inpost':

inpostprice    hrcprice    ... 213.57         null        ...

why hrcprice become null? sorry bad english. me in this!

you assigning null because have no else in case. assume instead want use old value:

update set     abcdtable.inpostprice = case when temptable.vendorname = 'inpost'                              temptable.price                               else abcdtable.inpostprice end,    .....

from documentation:

else else_result_expression: expression returned if no comparison operation evaluates true. if argument omitted , no comparison operation evaluates true, case returns null


Comments

Post a Comment

Popular posts from this blog

IF statement in MySQL trigger -

this query code in mysql trigger. if 1=2 select 'yes'; else select 'no'; end if; but has following error: if '1'='2' select 'yes' error code: 1064. have error in sql syntax; check manual corresponds mysql server version right syntax use near 'if '1'='2' select 'yes'' @ line 1 0.000 sec what solution?
Read more

c++ - What does MSC in "// appease MSC" comments mean? -

i run across // appease msc in c++ comments, e.g. here : const int& array::operator[](int offset) const { int mysize = getitssize(); if (offset >= 0 && offset < getitssize()) return ptype[offset]; throw xboundary(); return ptype[offset]; // appease msc! } what msc stand ? the author means microsoft's compiler, more formally known msvc.
Read more

javascript - Blogger related post gadget image Resize s72-c [ Need Expert Help ] -

i run blogger blog , use follow code javascript code (requires no jquery) show related post labels/categories of post. <script type='text/javascript'> var defaultnoimage=&quot;http://1.bp.blogspot.com/-m72rpguntq0/vuokijudn_i/aaaaaaaaboi/lq18sceunsg/w72/favicon-tik.png&quot;; var maxresults=16; var splittercolor=&quot;#d4eaf2&quot;; var relatedpoststitle=&quot;related posts&quot;; </script> <script type='text/javascript'>//<![cdata[ var relatedtitles = new array(); var relatedtitlesnum = 0; var relatedurls = new array(); var thumburl = new array(); function related_results_labels_thumbs(json) { (var = 0; < json.feed.entry.length; i++) { var entry = json.feed.entry[i]; relatedtitles[relatedtitlesnum] = entry.title.$t; try { thumburl[relatedtitlesnum] = entry.media$thumbnail.url } catch (error) {...
Read more