sql - 组合更新查询与单个更新语句

Question

任何机构都可以建议如何将以下 4 个更新语句变成单个更新语句。

update dbo.FACOD set LowValue ='07/02/2010' where Fieldname in ('AYStartDate','Dateloanstart')and AwardYear='2012-13'
update dbo.FACOD set LowValue ='07/02/2011' where Fieldname in ('AYStartDate','Dateloanstart')and AwardYear='2013-14'

update dbo.FACOD set HighValue='06/30/2015' where Fieldname in ('AYEndDate','DateloanEnd')and AwardYear='2012-13'
update dbo.FACOD set HighValue='06/30/2016' where Fieldname in ('AYEndDate','DateloanEnd')and AwardYear='2013-14'

试过下面一个

UPDATE  dbo.FACOD
SET     LowValue = CASE WHEN Fieldname in ('AYStartDate','Dateloanstart')and AwardYear='2012-13' THEN '07/02/2010' ELSE LowValue END,
        HighValue = CASE WHEN Fieldname in ('AYEndDate','DateloanEnd')and AwardYear='2012-13'THEN '06/30/2015'ELSE HighValue END,

但它并不能满足所有的更新语句。

Answer 1

请尝试:

update 
    dbo.FACOD 
set 
    LowValue=(case when Fieldname in ('AYStartDate','Dateloanstart')and AwardYear='2012-13' then '07/02/2010' 
                    when Fieldname in ('AYStartDate','Dateloanstart')and AwardYear='2013-14' then '07/02/2011' 
                else LowValue end),

    HighValue=(case when Fieldname in ('AYEndDate','DateloanEnd')and AwardYear='2012-13' then '06/30/2015' 
                    when Fieldname in ('AYEndDate','DateloanEnd')and AwardYear='2013-14' then '06/30/2016' 
                else HighValue end)
where 
    Fieldname in ('AYStartDate', 'Dateloanstart', 'AYEndDate', 'DateloanEnd')and 
    AwardYear IN ('2012-13', '2013-14')