SQL: how to update or insert if does not exist?

Question

I have an SQL insert below that 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 a new row. Is this possible with SQL?

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

Trying to use 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+"');"); 

For instance:

if the following was in the database and John wanted to update his watch, he would check the name, date, task, the same as the values ​​to be inserted, and if they only update HOURS. Otherwise, if none of them existed together (John may have a clock recorded against another DATE or JOB), insert a new line.

Others will also record hours and different roles in the same database as below.

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

Answer 1

You can use REPLACE INTO as follows:

 REPLACE INTO mytable (NAME, DATE, JOB, HOURS) VALUES ('BOB', '12/01/01', 'PM','30') 

But for this you need to create a unique index:

 CREATE UNIQUE INDEX myindex ON mytable(NAME, DATE, JOB) 

Please note that you should use a combination of fields for a unique index that will determine if two rows are considered the same and should be replaced, not inserted.

SQLFiddle Demo .

Please note that if you comment out on the unique creation of the index, it stops working correctly .

Answer 2

I think this was asked here earlier for sqlite:

INSERT IF A GENERAL UPDATE DOES NOT EXIST?

it looks like they have syntax:

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