delete (别名) from tblA (别名) left join tblb (别名) on。。。用法
1、创建使用的表及数据
CREATE TABLE YSHA
(
code VARCHAR(10),
NAME VARCHAR(20)
)
CREATE TABLE YSHB
(
code VARCHAR(10),
col VARCHAR(10)
)
INSERT INTO YSHA VALUES (1,'A1')
INSERT INTO YSHA VALUES (2,'A2')
INSERT INTO YSHB VALUES(1,'Row1')
INSERT INTO YSHB VALUES(1,'Row2')
INSERT INTO YSHB VALUES(1,'Row3')
INSERT INTO YSHB VALUES(3,'Row1')
2、delete from left join
delete from用法
DELETE A FROM YSHA A LEFT JOIN YSHB B ON A.code=b.code WHERE b.code is NULL
SELECT * FROM YSHA
SELECT * FROM YSHB
等同于
DELETE FROM YSHA WHERE NOT EXISTS(SELECT 1 FROM YSHB B WHERE YSHA.code=b.code )
SELECT * FROM YSHA
SELECT * FROM YSHB
以上操作是将B表中未使用的A表编号删除
3、delete from A,B
delete from 用法
DELETE A FROM YSHA A , YSHB B WHERE A.code=b.code
SELECT * FROM YSHA
SELECT * FROM YSHB
等同于
DELETE FROM YSHA WHERE EXISTS(SELECT 1 FROM YSHB B WHERE YSHA.code=b.code )
SELECT * FROM YSHA
SELECT * FROM YSHB