Rock的SQL筆記本

測試語法如下

Create Table t1(col1 int,col2 int,col3 int);
Create Table t2(col1 int,col2 int,col3 int);

insert into t1(col1,col2,col3) values(111,1,1),(111,2,1),(111,3,1),(111,4,1),(111,5,1),(111,6,1);
insert into t1(col1,col2,col3) values(110,1,1),(110,2,1),(110,3,1),(110,4,1),(110,5,1),(110,6,1);

insert into t2(col1,col2,col3) values(111,1,1),(111,2,1),(111,3,1);
insert into t2(col1,col2,col3) values(110,1,1),(110,2,1),(110,3,1),(110,4,1),(110,5,1),(110,6,1);

select * from t1 Where col1=111;

With tmp1 as(Select * From t1 Where col1=111)
,tmp2 as(Select * From t2 Where col1=111)
Delete tmp1 From tmp1 a
left join tmp2 b on a.col2=b.col2
where b.col3 is null;

select * from t1 Where col1=111;

上述語法邏輯是想將t1及t2的col1=111的資料作比對後，將比對不到的資料從t1來移除。但上述執行後會發現t1中所有col1=111的都被刪除

語法改寫如下就可以正常刪除資料

With tmp1 as(Select * From t1 Where col1=111)
,tmp2 as(Select * From t2 Where col1=111)
Delete a From tmp1 a
left join tmp2 b on a.col2=b.col2
where b.col3 is null;

所以日後用CTE來刪資料得好好注意這一塊

我是ROCK

rockchang@mails.fju.edu.tw