Update case statement not updating

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

Update case statement not updating

Author

Topic

rmrowlands
Starting Member

1 Post

Posted - 2011-03-04 : 06:43:50

I'm using the update case statement (first time) to select the lowest value in a number of columns(13). However every time it runs it looks to run successfully ((66 row(s) affected)) but it's not updating my table. Extract of my sql is

UPDATE GAME_CHANGER_EXTRACT_RANK
SET GC_PERF_ACTION =
CASE
WHEN ((GC_TAG11_RANK < GC_TAG12_RANK) AND (GC_TAG11_RANK < GC_TAG13_RANK)) THEN ACTION_TAG11
ELSE ACTION_TAG13 END

I know I must be missing something obvious.
Any ideas.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-03-04 : 07:18:59

you can find more on CASE Expressions When Modifying Data in below link
http://www.craigsmullins.com/ssu_0899.htm

--Ranjit

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-04 : 08:01:07

Looks correct to me. Run this query and see if the first column shows the values that you are expecting and if the first and second columns are the same.

--UPDATE GAME_CHANGER_EXTRACT_RANK
--SET GC_PERF_ACTION =
select
CASE 
WHEN ((GC_TAG11_RANK < GC_TAG12_RANK) AND (GC_TAG11_RANK < GC_TAG13_RANK)) THEN ACTION_TAG11
ELSE ACTION_TAG13 END,
GC_PERF_ACTION from GAME_CHANGER_EXTRACT_RANK

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-05 : 15:26:55

>> I'm using the UPDATE case statement (first time) to select the lowest value in a number of columns(13). <<

Wrong approach! This value is computed and should not be stored in the table. You will have to constantly update this table before you can use it.

Also, Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

This looks like repeated group violation of First Normal Form. What are you trying to do and what are you trying to do it with?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources