#
# DuckDB DELETE operations test
#
# Setup
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (
id INT PRIMARY KEY,
val VARCHAR(50)
) ENGINE = DuckDB;
CREATE TABLE t2 (
id INT PRIMARY KEY,
val VARCHAR(50)
) ENGINE = DuckDB;
INSERT INTO t1 VALUES (1, 'one');
INSERT INTO t1 VALUES (2, 'two');
INSERT INTO t1 VALUES (3, 'three');
INSERT INTO t1 VALUES (4, 'four');
INSERT INTO t1 VALUES (5, 'five');
INSERT INTO t2 VALUES (1, 'alpha');
INSERT INTO t2 VALUES (3, 'beta');
INSERT INTO t2 VALUES (5, 'gamma');
# Test 1: DELETE single row by PK
DELETE FROM t1 WHERE id = 1;
SELECT * FROM t1 ORDER BY id;
id val
2 two
3 three
4 four
5 five
# Test 2: DELETE by non-PK condition
DELETE FROM t1 WHERE val = 'three';
SELECT * FROM t1 ORDER BY id;
id val
2 two
4 four
5 five
# Test 3: DELETE with range condition
DELETE FROM t1 WHERE id >= 4;
SELECT * FROM t1 ORDER BY id;
id val
2 two
# Test 4: DELETE all rows
DELETE FROM t1;
SELECT * FROM t1 ORDER BY id;
id val
SELECT COUNT(*) FROM t1;
COUNT(*)
0
# Test 5: DELETE with subquery
INSERT INTO t1 VALUES (1, 'one');
INSERT INTO t1 VALUES (2, 'two');
INSERT INTO t1 VALUES (3, 'three');
INSERT INTO t1 VALUES (4, 'four');
INSERT INTO t1 VALUES (5, 'five');
DELETE FROM t1 WHERE id IN (SELECT id FROM t2);
ERROR HY000: Storage engine DUCKDB of the table `db_dml_delete`.`t2` doesn't have this option
SELECT * FROM t1 ORDER BY id;
id val
1 one
2 two
3 three
4 four
5 five
# Test 6: DELETE in transaction — COMMIT
BEGIN;
DELETE FROM t1 WHERE id = 2;
SELECT * FROM t1 ORDER BY id;
id val
1 one
3 three
COMMIT;
SELECT * FROM t1 ORDER BY id;
id val
1 one
3 three
# Test 7: DELETE in transaction — ROLLBACK
BEGIN;
DELETE FROM t1 WHERE id = 2;
SELECT * FROM t1 ORDER BY id;
id val
1 one
3 three
ROLLBACK;
SELECT * FROM t1 ORDER BY id;
id val
1 one
2 two
3 three