假设我有一个空表'tbl_test',其中有3列,没有主键;
column1和column2是字符串,column3是int
然后我设置了一个函数myFunc(column1Data,column2Data){
//我希望它像这样
如果我的表中存在column1Data AND column2Data,它将更新并
增加我的column3
否则,如果column1Data或column2Data不存在,它将插入
column1Data,column2Data和column3将设置为0
}
我已经尝试过此解决方案https://stackoverflow.com/a/4330694
这是我的代码:https://www.db-fiddle.com/f/cE1FSJawQJFjSHsk93Lrjt/0
CREATE TABLE tbl_test (
column1 varchar(255),
column2 varchar(255),
column3 int(12)
);
INSERT OR REPLACE INTO tbl_test (column1, column2, column3)
VALUES ( COALESCE((SELECT column1 FROM tbl_test WHERE column1 = 'column1Data'), 'column1Data') ,
COALESCE((SELECT column2 FROM tbl_test WHERE column2 = 'column2Data'), 'column2Data') ,
COALESCE((SELECT SUM(column3+1) AS Total FROM tbl_test WHERE column1 = 'column1Data' AND column2 = 'column2Data'), 0)
);
INSERT OR REPLACE INTO tbl_test (column1, column2, column3)
VALUES ( COALESCE((SELECT column1 FROM tbl_test WHERE column1 = 'column1Data'), 'column1Data') ,
COALESCE((SELECT column2 FROM tbl_test WHERE column2 = 'column2Data'), 'column2Data') ,
COALESCE((SELECT SUM(column3+1) AS Total FROM tbl_test WHERE column1 = 'column1Data' AND column2 = 'column2Data'), 0)
);
INSERT OR REPLACE INTO tbl_test (column1, column2, column3)
VALUES ( COALESCE((SELECT column1 FROM tbl_test WHERE column1 = 'column1Data'), 'column1Data') ,
COALESCE((SELECT column2 FROM tbl_test WHERE column2 = 'column2DataNew'), 'column2DataNew') ,
COALESCE((SELECT SUM(column3+1) AS Total FROM tbl_test WHERE column1 = 'column1Data' AND column2 = 'column2DataNew'), 0)
);
如果column1Data或column2Data不存在,则会插入新数据,并将column3 = 0设置为正确
但是,如果确实存在column1Data和column2Data,它将插入新行并递增column3,而不是对其进行更新
最佳答案
在SQLite 3.24+中,您可以使用upsert
。这从前两列的唯一约束开始:
create unique index unq_tbl_test_column1_column2 on tbl_test(column1, column2);
创建列3时,将其默认值添加为0。
然后,您可以将所需内容表达为:
insert into tbl_test (column1, column2)
values (?, ?)
on conflict (column1, column2)
do update set column3 = column3 + 1;