delete與truncate指令

 

先準備範例資料表

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','商業會員');

 

一、delete語法

DELETE [ FROM ] table_or_view_name

WHERE search_condition

1、將Bill從ORDER資料表刪除

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

DELETE [ORDER] 
WHERE name = 'Bill'

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

 

2、刪除ORDER資料表所有資料

DELETE FROM [ORDER]

或

DELETE [ORDER]

都可以

 

3、參考其他資料來源的DELETE

語法：

DELETE [ FROM ] table_or_view_name

FROM table_source

WHERE search_condition

 

例1：把VIP會員的訂單全都刪除

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

SELECT name,level
FROM VIP

DELETE [ORDER] 
FROM VIP
WHERE [ORDER].name = VIP.name

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

SELECT name,level
FROM VIP

 

例2：把非VIP會員的訂單全都刪除

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

SELECT name,level
FROM VIP

DELETE [ORDER] 
FROM VIP
WHERE [ORDER].name <> VIP.name

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

SELECT name,level
FROM VIP

但你會發現例2的結果有點奇怪，明明只要保留VIP Bill與Mary的訂單，

但怎麼會全部刪掉呢？其實問題出在「WHERE [ORDER].name <> VIP.name」

這種「參考其他資料來源的DELETE」其WHERE部份應該只能使用

「WHERE [ORDER].name = VIP.name」語法，其功能倒比較像是「JOIN交集」，

而不是「WHERE條件篩選」，如果要「把非VIP會員的訂單全都刪除」的話，

可以使用子查詢的寫法

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

SELECT name,level
FROM VIP

DELETE [ORDER] 
WHERE  [ORDER].name NOT IN (SELECT name 
                            FROM   VIP) 

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

SELECT name,level
FROM VIP

 

二、truncate語法

TRUNCATE TABLE table_or_view_name

1、刪除ORDER資料表所有資料

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

TRUNCATE TABLE [ORDER] 

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

 

當使用DELETE敘述刪除資料表內的全部資料，

也就是沒有使用WHERE來篩選資料時，結果會如同使用TRUNCATE敘述，

刪除資料表內的所有資料，但是就資料庫管理糸統處理過程來說是不相同的。

 

DELETE table_name：使用DELETE敘述，會先將所有刪除的動作和資料全寫入「記錄檔」(log file)，

再從「記錄檔」更新至「資料檔」(data file)。

TRUNCATE TABLE table_name：使用TRUNCATE敘述，是不記錄個別資料列的刪除動作，

會使用較少的糸統資源和記錄檔空間，所以執行速度會較快。除此之外，

若是在該資料表中有使用到「識別資料」型態的欄位，將會「識別值種子」重設，

也就是從初始值開始。