SQL:如果不存在，如何更新或插入? [英] SQL: How to update or insert if doesn't exist?

问题描述

我在下面有一个 SQL 插入，它工作正常，但是我希望它检查 DATE=xxxx、NAME =xxxx 和 JOB = xxx 并更新 HOURS(如果它们存在)，否则插入新行.这可以用 SQL 实现吗?

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?

"INSERT INTO TABLE (NAME, DATE, JOB, HOURS) VALUES ('BOB', '12/01/01', 'PM','30');

尝试下面的 OR REPLACE 得到相同的结果，每次都会添加一个新行.

Trying the below OR REPLACE with the same results, a new line is added each time.

add_time = conn.prepareStatement("INSERT OR REPLACE INTO RESOURCE (NAME, DATE, JOB, HOURS) VALUES ('"+name+"', '" + date + "', '"+job+"','"+hours+"');");

例如:

如果下面的内容在数据库中，并且 John 想要更新他的工作时间，它会检查姓名、日期、工作是否与尝试插入的值相同，如果它们只是更新 HOURS.否则，如果它们都不存在(约翰可能已经记录了另一个 DATE 或 JOB 的小时数)，则插入一个新行.

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.

其他人也会在同一个数据库中记录他们的工作时间和不同的角色，如下所示.

Also others will also log their hours and different roles in the same DB as below.

约翰 |12/12/2012 |清洁剂 |20约翰 |12/12/2012 |首席执行官 |10吉姆 |12/10/2011 |清洁剂 |5

John | 12/12/2012 | Cleaner | 20 John | 12/12/2012 | ceo | 10 Jim | 12/10/2011 | Cleaner | 5

推荐答案

你可以像这样使用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.

SQLFiddle 演示.

请注意，如果您注释掉唯一索引创建，它停止正常工作.

Note that if you comment out unique index creation, it stops working correctly.

这篇关于SQL:如果不存在，如何更新或插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！