update指令

 

先準備範例資料表

CREATE TABLE [ORDER] 
  ( 
     no      INT NOT NULL, 
     name    [NVARCHAR](10) NOT NULL, 
     year    SMALLINT NOT NULL, 
     quarter TINYINT NOT NULL, 
     item    [NVARCHAR](10) NOT NULL, 
     price   [INT] NOT NULL, 
     amount  [INT] NOT NULL, 
     PRIMARY KEY (no) 
  ) 

go 

INSERT [ORDER] VALUES (1,'Bill',2005,1,'CPU',3000,1);
INSERT [ORDER] VALUES (2,'Bill',2005,2,'RAM',2000,4);
INSERT [ORDER] VALUES (3,'Bill',2006,4,'MB',2000,1);
INSERT [ORDER] VALUES (4,'Mary',2010,1,'SSD',2500,5);
INSERT [ORDER] VALUES (5,'Mary',2010,1,'LCD',6700,1);
INSERT [ORDER] VALUES (6,'Bryan',2015,1,'KB',500,2);
INSERT [ORDER] VALUES (7,'Bryan',2015,3,'ROUTER',1500,2);
INSERT [ORDER] VALUES (8,'Tim',2016,1,'CPU',12000,1);
INSERT [ORDER] VALUES (9,'Tim',2016,4,'MB',5000,1);



CREATE TABLE VIP 
  ( 
     name NVARCHAR(10) NOT NULL,
     level CHAR(8), 
     PRIMARY KEY (name) 
  ) 

go

INSERT [VIP] VALUES ('Bill','金級會員');
INSERT [VIP] VALUES ('Mary','商業會員');

 

一、UPDATE的基本語法

UPDATE table_view_name

SET { column_name = { expression | DEFAULT | NULL } } [,...n]

WHERE search_condition

 

二、範例

1、條件式更新

SELECT name,level 
FROM   VIP 

UPDATE VIP 
SET    level = '金級會員' 
WHERE  name = 'Mary' 

SELECT name,level 
FROM   VIP

 

2、同時更新多個資料行

SELECT name,level 
FROM   VIP 

UPDATE VIP 
SET    name = 'Mary Lin', level = '商業會員' 
WHERE  name = 'Mary' 

SELECT name,level 
FROM   VIP

 

3、更新資料為空值

SELECT name,level 
FROM   VIP 

UPDATE VIP 
SET    level = NULL
WHERE  name = 'Mary Lin' 

SELECT name,level 
FROM   VIP

 

4、利用運算式更新資料

SELECT name,level 
FROM   VIP 

UPDATE VIP 
SET    level = '商業' + '會員'
WHERE  name = 'Mary Lin' 

SELECT name,level 
FROM   VIP

 

5、參考其他資料來源的UPDATE

進階語法

UPDATE table_view_name

SET { column_name = { expression | DEFAULT | NULL } } [,...n]

FROM table_source

WHERE search_condition

 

(1)、

對ORDER訂單資料表作修改，當訂單裡的會員具有VIP的身份時，

則對該筆訂單的單價打八折。

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER]

SELECT name,level 
FROM   VIP 

UPDATE [ORDER] 
SET    price = price * 0.8
FROM   VIP 
WHERE  [ORDER].name =  VIP.name 

SELECT no,name,year,quarter,item,price,amount 
FROM   [ORDER]

 

(2)、針對上一種寫法，不能使用別名，若要使用別名則使用

UPDATE O 
SET    price = price * 0.8
FROM   [ORDER] O , VIP V 
WHERE  O.name =  V.name