下面我們建立一張和 COMPANY 表相似的 COMPANY1 表,使用 DELETE 語句和 WITH 子句删除 COMPANY 表中 SALARY(工資) 字段大于等于 30000 的資料,并将删除的資料插入 COMPANY1 表,實作将 COMPANY 表資料轉移到 COMPANY1 表中:
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
WITH moved_rows AS (
DELETE FROM COMPANY
WHERE
SALARY >= 30000
RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows
得到結果如下:
INSERT 0 3