SQLite DELETE 语句中的子查询 - SQLite 基础教程

SQLite 中子查询可以与 DELETE 语句结合使用

语法如下

DELETE FROM tablename [ WHERE fieldname [op] 
   (SELECT fieldname2 FROM tablename2  [WHERE] ) ]

例如语句

DELETE FROM tbl_employee WHERE age IN 
  (SELECT age FROM tbl_age WHERE age > 27 );

用于删除 tbl_employee 中的年龄在 tbl_age 中大于 27 岁的那些记录

范例

先运行下面的 SQLite SQL 语句创建测试数据

有两张表，一张是职工表，一张是年龄表

DROP TABLE tbl_employee;
DROP TABLE tbl_age;

CREATE TABLE tbl_employee (
   id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL,
   name           CHAR(64) NOT NULL,
   age            INTEGER  NOT NULL,
   city           CHAR(64),
   salary         REAL
);


CREATE TABLE tbl_age (
   id      INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL,
   age  INTEGER  NOT NULL
);


INSERT INTO tbl_employee (name,age,city,salary) VALUES
('张三', 25, 'pek', 102400.00 ),
('李四', 18, 'shanghai', 88888.00 ),
('王五', 22, 'hangzhou', 22000.00 ),
('孙六', 32, 'pek', 32000.00 ),
('钱七', 25, 'shanghai', 25000.00 ),
('赵二', 45, 'pek', 45800.00 ),
('冯九', 37, 'chengdu', 15000.00 );

INSERT INTO tbl_age (age) VALUES
(10),
(15),
(28),
(35),
(37);

我们先使用下面的语句看看要删除的记录有哪些

SELECT * FROM tbl_employee WHERE age IN 
  (SELECT age FROM tbl_age WHERE age > 27 );

语句输出结果如下

id          name        age         city        salary    
----------  ----------  ----------  ----------  ----------
7           冯九          37          chengdu     15000.0

也就是我们要删除第七条记录

然后执行以下语句删除

DELETE FROM tbl_employee WHERE age IN 
  (SELECT age FROM tbl_age WHERE age > 27 );

使用 SELECT * FROM tbl_employee; 看看删了哪条

id          name        age         city        salary    
----------  ----------  ----------  ----------  ----------
1           张三          25          pek         102400.0  
2           李四          18          shanghai    88888.0   
3           王五          22          hangzhou    22000.0   
4           孙六          32          pek         32000.0   
5           钱七          25          shanghai    25000.0   
6           赵二          45          pek         45800.0

可以看到第七条记录的确删除了