Use CASE in the UPDATE statement : Case « Select Query « SQL Server / T-SQL






  1. SQL Server / T-SQL
  2. Select Query
  3. Case

Use CASE in the UPDATE statement

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>








Related examples in the same category

1.Select case statement
2.Case when and else
3.CASE: return a specified value based on a set of business logic
4.'Case when' statement in a procedure
5.Case When with else
6.Case when NULL
7.Case when with Mod and ABS()
8.A simple CASE takes an expression that equates to a Boolean result