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:
Post a Comment