CODEWITHSUNDEEP
sqlsql-server
user2500796
user2500796

Reputation: 117

Combined Update Query with single update statement

Can any Body suggest how to make the below 4 update statement to single update statement.

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'

tried with below one

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,

But it is not satisfying all the update statement.

Upvotes: 2

Views: 194

Answers (2)

user2500796
user2500796

Reputation: 117

Here is the Correct ANswer

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'
        )

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try:

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')

Upvotes: 3

Related Questions