Mega Code Archive
Use CASE in the UPDATE statement
<![CDATA[
27>
28> CREATE TABLE project (project_no CHAR(4) NOT NULL,
29> project_name CHAR(15) NOT NULL,
30> budget FLOAT NULL)
31>
32> insert into project values ('p1', 'Search Engine', 120000.00)
33> insert into project values ('p2', 'Programming', 95000.00)
34> insert into project values ('p3', 'SQL', 186500.00)
35>
36> select * from project
37> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 120000
p2 Programming 95000
p3 SQL 186500
(3 rows affected)
1>
2> -- Use CASE in the UPDATE statement.
3>
4> UPDATE project SET budget = CASE
5> WHEN budget > 0 and budget < 100000 THEN budget* 1.2
6> WHEN budget > = 100000 and budget < 200000 THEN budget* 1.1
7> ELSE budget* 1.05
8> END
9> GO
(3 rows affected)
1> select * from project
2> GO
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 132000
p2 Programming 114000
p3 SQL 205150.00000000003
(3 rows affected)
1> drop table project
2> GO
1>
2>
]]>