Updating Data Using the CASE Expression

http://www.java2s.com/Tutorial/SQLServer/0040__Insert-Delete-Update/UpdatingDataUsingtheCASEExpression.htm

6>
7> CREATE TABLE sales(
8>    stor_id char(4) NOT NULL,
9>    ord_num        varchar(20) NOT NULL,
10>    ord_date       datetime          NOT NULL,
11>    qty            smallint          NOT NULL,
12>    payterms       varchar(12) NOT NULL,
13>    title_id       varchar(80)
14> )
15> GO
1> insert sales values('1', 'QA7442.3', '09/13/94', 75, 'ON Billing','1')
2> insert sales values('2', 'D4482',    '09/14/94', 10, 'Net 60', '1')
3> insert sales values('3', 'N914008',  '09/14/94', 20, 'Net 30', '2')
4> insert sales values('4', 'N914014',  '09/14/94', 25, 'Net 30', '3')
5> insert sales values('5', '423LL922', '09/14/94', 15, 'ON Billing','3')
6> insert sales values('6', '423LL930', '09/14/94', 10, 'ON Billing','2')
7> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>
3> UPDATE sales
4>     SET payterms =
5>         CASE
6>             WHEN (SELECT SUM(qty) FROM sales s1
7> WHERE sales.stor_id = s1.stor_id) < 10
8>                 THEN 'On Billing'
9>             WHEN (SELECT SUM(qty) FROM sales s1
10> WHERE sales.stor_id = s1.stor_id) < 100
11>                 THEN 'Net 30'
12>             ELSE 'Net 60'
13> END
14>     GO

(6 rows affected)
1>
2> SELECT stor_id, SUBSTRING(ord_num,1,5) ord_num,
3>             ord_date, qty, payterms, title_id FROM sales
4>     GO
stor_id ord_num ord_date                qty    payterms     title_id
------- ------- ----------------------- ------ ------------ --------------------------------------------------------------------------------
1 QA744 1994-09-13 00:00:00.000     75 Net 30       1
2 D4482 1994-09-14 00:00:00.000     10 Net 30       1
3 N9140 1994-09-14 00:00:00.000     20 Net 30       2
4 N9140 1994-09-14 00:00:00.000     25 Net 30       3
5       423LL 1994-09-14 00:00:00.000     15 Net 30       3
6       423LL 1994-09-14 00:00:00.000     10 Net 30       2

(6 rows affected)
1>
2> drop table sales;
3> GO