Thursday, September 25, 2008

SQL Case Expression:

An SQL case expression offers a simple way to add conditional evaluation to an SQL statement. It can often simplify what would otherwise be a difficult or even impossible task.
Use:
Multiple updates based on multiple conditions, in one pass could be done by SQL case expressions:
E.g.:
The following two SQL statements can be combined into one. The single statement may run faster, especially against a large file, since it makes only one pass.
Update filename set field1 = 'Y1' where field1 = 'X1'
Update filename set field2 = 'Y2' where field2 = 'X2'
Single statement:
Update filename set
field1 = CASE
When field1 = 'X1' then 'Y1'
Else field1
END,
field2 = CASE
When field2 = 'X2' then 'Y2'
Else field2
END
Where field1 = 'X1' or field2 = 'X2'

No comments: