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

Home
SQL Server / T-SQL
1.Aggregate Functions
2.Analytical Functions
3.Constraints
4.Cursor
5.Data Set
6.Data Type
7.Database
8.Date Timezone
9.Index
10.Insert Delete Update
11.Math Functions
12.Select Query
13.Sequence
14.Store Procedure Function
15.String Functions
16.Subquery
17.System
18.Table
19.Table Joins
20.Transact SQL
21.Transaction
22.Trigger
23.View
24.XML
SQL Server / T-SQL » Select Query » Case 




Use CASE in the UPDATE statement

27>
28CREATE TABLE project   (project_no   CHAR(4NOT NULL,
29>                         project_name CHAR(15NOT NULL,
30>                         budget FLOAT NULL)
31>
32insert into project values ('p1', 'Search Engine',        120000.00)
33insert into project values ('p2', 'Programming',          95000.00)
34insert into project values ('p3', 'SQL',                  186500.00)
35>
36select from project
37> GO

(rows affected)

(rows affected)

(rows affected)
project_no project_name    budget
---------- --------------- ------------------------
p1         Search Engine                     120000
p2         Programming                        95000
p3         SQL                               186500

(rows affected)
1>
2> -- Use CASE in the UPDATE statement.
3>
4UPDATE project SET budget = CASE
5>           WHEN budget > 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

(rows affected)
1select from project
2> GO
project_no project_name    budget
---------- --------------- ------------------------
p1         Search Engine                     132000
p2         Programming                       114000
p3         SQL                   205150.00000000003

(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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.