What I want to do is to just increment the 4th column of the value that it should've been if it didn't existed, when a row that has col0 = valuecol0 AND col2 = valuecol2 is in the table. Otherwise, I want to insert the row, of course.
我要做的是增加第4列的值，如果它不存在，当表中有col0 = valuecol0和col2 = valuecol2的行。否则，我当然要插入行。
INSERT INTO tablename (col0, col1, col2, col3)
VALUES (valuecol0, valuecol1, valuecol2, valuecol3)
ON DUPLICATE KEY UPDATE col3 = col3 + VALUES( col3 );
Anyway, I can't figure out how I should set the INDEXes in the table, I read the page in the documentation here but it wans't much of help. So I tried all the four possible combination and no one worked!
无论如何，我不知道如何在表中设置索引，我在这里阅读了文档中的页面，但是这没有多大帮助。所以我尝试了四种可能的组合，但没有人成功!
Where am I failing?
我失败的什么地方?
1 个解决方案
#1
4
To generate a key violation, you can use a unique index:
要生成密钥违例，可以使用唯一索引:
create unique index IX_YourTable_Col0Col2 on YourTable (col0, col2);
With this index, you can use the on duplicate key update syntax:
有了这个索引，您可以使用on duplicate key update语法:
insert into YourTable (col0, col1, col2, col3)
values (1, 2, 1, 2)
on duplicate key update col3 = col3 + 2;
示例SQL小提琴。
#1
4
To generate a key violation, you can use a unique index:
要生成密钥违例，可以使用唯一索引:
create unique index IX_YourTable_Col0Col2 on YourTable (col0, col2);
With this index, you can use the on duplicate key update syntax:
有了这个索引，您可以使用on duplicate key update语法:
insert into YourTable (col0, col1, col2, col3)
values (1, 2, 1, 2)
on duplicate key update col3 = col3 + 2;
示例SQL小提琴。