SQL：如果不存在，如何更新或插入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14807472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: How to update or insert if doesn't exist?
提问by Rhys
I have a SQL insert below, which works fine, however I would like it to check if DATE=xxxx, NAME =xxxx and JOB = xxx and update HOURS if they exist otherwise insert new row. IS this possible with SQL?
我在下面有一个 SQL 插入，它工作正常，但是我希望它检查 DATE=xxxx、NAME =xxxx 和 JOB = xxx 并更新 HOURS（如果它们存在），否则插入新行。这可以用 SQL 实现吗？
"INSERT INTO TABLE (NAME, DATE, JOB, HOURS) VALUES ('BOB', '12/01/01', 'PM','30');
Trying the below OR REPLACE with the same results, a new line is added each time.
用相同的结果尝试下面的 OR REPLACE，每次都会添加一个新行。
add_time = conn.prepareStatement("INSERT OR REPLACE INTO RESOURCE (NAME, DATE, JOB, HOURS) VALUES ('"+name+"', '" + date + "', '"+job+"','"+hours+"');");
For example:
例如：
if the below was in the DB, and John wanted to update his hours, it would check name, date, job were the same as the values trying to insert and if they are update HOURS only. Otherwise if none of them existed together ( John may have hours logged against another DATE or JOB) insert a new row.
如果下面的内容在数据库中，并且 John 想要更新他的工作时间，它会检查姓名、日期、工作是否与尝试插入的值相同，如果它们只是更新 HOURS。否则，如果它们都不存在（约翰可能已经针对另一个 DATE 或 JOB 记录了小时数），则插入一个新行。
Also others will also log their hours and different roles in the same DB as below.
其他人也会在同一个数据库中记录他们的工作时间和不同的角色，如下所示。
John | 12/12/2012 | Cleaner | 20 John | 12/12/2012 | ceo | 10 Jim | 12/10/2011 | Cleaner | 5
约翰 | 12/12/2012 | 清洁剂 | 20 约翰 | 12/12/2012 | 首席执行官 | 10 吉姆 | 12/10/2011 | 清洁剂 | 5
回答by mvp
You can use REPLACE INTOlike this:
你可以这样使用REPLACE INTO：
REPLACE INTO mytable (NAME, DATE, JOB, HOURS)
VALUES ('BOB', '12/01/01', 'PM','30')
But, you must create unique index for this to work:
但是，您必须为此创建唯一索引才能工作：
CREATE UNIQUE INDEX myindex
ON mytable(NAME, DATE, JOB)
Note, that you should use such combination of fields for unique index that will determine if two rows are considered the same and should be replaced rather than inserted.
请注意，您应该将此类字段组合用于唯一索引，以确定两行是否被视为相同并且应该替换而不是插入。
Note that if you comment out unique index creation, it stops working correctly.
请注意，如果您注释掉唯一索引创建，它将停止正常工作。
回答by Rhys
I think this has been asked here before for sqlite:
我认为这已经被问到这里之前的 sqlite 了：
INSERT IF NOT EXISTS ELSE UPDATE?
seems like they have a syntax for that:
似乎他们有一个语法：
INSERT OR REPLACE INTO TABLE (NAME, DATE, JOB, HOURS) VALUES ('BOB', '12/01/01', 'PM','30');
