SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, July 24, 2006

Case statement question

SQL Apprentice Question
Hi All,
Is it possible to update a specific field based on the case statement?
Something like this, for example:


UPDATE Person SET
CASE
WHEN HB = '1' THEN HasBrother = '1'
WHEN HS = '1' THEN HasSister = '1'
END
FROM
.....


I know one way is to specify both fields and have CASE statement for both of them, something like:
UPDATE Person SET
HasBrother = CASE
WHEN HB = '1' THEN '1'
ELSE HasBrother
END
HasSister = CASE
WHEN HB = '1' THEN '1'
ELSE HasSister
END
FROM
......


But I am curious if first example was possible.


Celko Answers
Better schema design and encodings! SQL is a data language and not a
procedural language.

CREATE TABLE Persons
( ..
sibling_status DEFAULT 0 INTEGER NOT NULL
CHECK (sibling_status IN (0, 1, 2, 3)),
..);


0= no siblings
1= has brothers
2= has sisters
3= has brothers and sisters



>> Of course, other than dynamically building the SQL statement. <<


Writing dynamic SQL is like playhing a video game where you have no
idea what will happen next, so you just start shooting at the problem
at run time :)

No comments: