我下面有一个SQL创建一个表。它将在名称冲突的地方替换。
CREATE TABLE IF NOT EXISTS MYTABLE (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT NOT NULL,
DATE INTEGER NOT NULL,
CONSTRAINT UNIQUE_NAME UNIQUE (NAME) ON CONFLICT REPLACE
)
但是,我只想在日期较新(数字较大)时才替换,或者在日期较旧时忽略新行。如何更改上面的SQL(用于SQLite)语句以实现该目标?
不能通过表约束来执行此操作,而必须使用触发器:
CREATE INDEX just_some_index ON MyTable(Name);
CREATE TRIGGER MyTable_Name_insert_newer
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT Date FROM MyTable WHERE Name = NEW.Name) <= NEW.Date
BEGIN
DELETE FROM MyTable
WHERE Name = NEW.Name;
END;
CREATE TRIGGER MyTable_Name_insert_older
BEFORE INSERT ON MyTable
FOR EACH ROW
WHEN (SELECT Date FROM MyTable WHERE Name = NEW.Name) > NEW.Date
BEGIN
SELECT RAISE(IGNORE);
END;
(在SQLite中,没有结果的标量子查询仅返回NULL,因此插入新行会使两个WHEN子句均失败。)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句