If else update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

If else update query

Author

Topic

cazziewhelan
Starting Member

7 Posts

Posted - 2014-02-03 : 11:45:58

Hi All,

Hope someone can help, its been years since ive used SQL and now trying to remembered in college a few years ago!

I am building a database to measure performance in business, based on targets and actual figures. There is a points system for those whos actual is equal or above there target figure. I want to build an update query, that will run and calculate points once actual figures are added to the database. I have managed ot write 2 seperate queries, one to update with points when hey exceed target and one to award no points if the exceed below the taret, but how can I combine these 2 queries as one, with an if else structure?

To update when they exceed target
Here are there queries I have working so far:

UPDATE Result_Details SET Result_Details.Result_Value = "60"
WHERE (((Result_Details.Actual_Value)>=[Target_Value]) AND ((Result_Details.KPI_Name)="Productivity"));

UPDATE Result_Details SET Result_Details.Result_Value = "0"
WHERE (((Result_Details.Actual_Value)<=[Target_Value]));

Also, I have stored the point values in another table, and called them KPI_Pass and KPI_Fail, and tried to pass these paramaters into this query instead of the 60 value, but I was getting syntax error!

Hope someone out there can help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-03 : 13:37:03

[code]
UPDATE Result_Details SET Result_Details.Result_Value =CASE WHEN (((Result_Details.Actual_Value)>=[Target_Value]) AND ((Result_Details.KPI_Name)="Productivity")) THEN 60 WHEN (((Result_Details.Actual_Value)<=[Target_Value])) THEN 0 ELSE Result_Value END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-03 : 14:01:32

Dilemma: if Actual_Value=Target_Value and KPI_Name=Productivity, should the Result_Value be set to 60 or 0?

Anyway, try this:

update Result_Details
   set Result_Value=case
                       when Actual_Value>=Target_Value
                        and KPI_Name='Productivity'
                       then a.KPI_PASS
                       else a.KPI_FAIL
                    end
       )
  from another_table as a
 where (Actual_Value>=Target_Value
   and  KPI_Name='Productivity'
       )
    or Actual_Value<Target_Value

Ps.: I don't have access to a database server, so syntax errors might occur.

cazziewhelan
Starting Member

7 Posts

Posted - 2014-02-04 : 03:52:48

Thanks for the reply visakh16[/i]

I have tried the code you provided, however I am recieveing a Syntax error stating that there is a missing operator in CASE WHEN?

Any ideas?

UPDATE Result_Details SET Result_Details.Result_Value =CASE WHEN (((Result_Details.Actual_Value)>=[Target_Value]) AND ((Result_Details.KPI_Name)="Productivity")) THEN 60 WHEN (((Result_Details.Actual_Value)<=[Target_Value])) THEN 0 ELSE Result_Value END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

[/quote]

cazziewhelan
Starting Member

7 Posts

Posted - 2014-02-04 : 03:59:26

Thanks bitsmed

I have also tried this code and recieving a syntax error, stating I am missing an operator? I have updated a reference with correct table name! Any ideas why this error is occuring? Thanks :)

update Result_Details
set Result_Value=case
when Actual_Value>=Target_Value
and KPI_Name='Productivity'
then KPI_Details.KPI_PASS
else KPI_Details.KPI_FAIL
end
)
from Result_Details
where (Actual_Value>=Target_Value
and KPI_Name='Productivity'
)
or Actual_Value<Target_Value

quote:

Originally posted by bitsmed

Dilemma: if Actual_Value=Target_Value and KPI_Name=Productivity, should the Result_Value be set to 60 or 0?

Anyway, try this:

update Result_Details
   set Result_Value=case
                       when Actual_Value>=Target_Value
                        and KPI_Name='Productivity'
                       then a.KPI_PASS
                       else a.KPI_FAIL
                    end
       )
  from another_table as a
 where (Actual_Value>=Target_Value
   and  KPI_Name='Productivity'
       )
    or Actual_Value<Target_Value

Ps.: I don't have access to a database server, so syntax errors might occur.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-04 : 06:11:40

below code will work in SQL Server
[code]
UPDATE Result_Details
SET Result_Value =CASE WHEN Actual_Value>=[Target_Value]
AND KPI_Name = 'Productivity' THEN 60
WHEN Actual_Value <=[Target_Value] THEN 0
ELSE Result_Value
END
[/CODE]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources