8

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');

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

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.

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

Rhys
  • 2,807
  • 8
  • 46
  • 68

2 Answers2

10

You can use REPLACE INTO like this:

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 Demo.

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

mvp
  • 111,019
  • 13
  • 122
  • 148
  • So, which INDEX's do I set to unique? HOURS is the one that I want to change if the others exist. – Rhys Feb 11 '13 at 07:46
  • Well, you asked to insert or replace if rows are *the same*, correct? So, you must create unique index on that combination of fields which will determine what two rows are considered *the same*, and should be replaced rather than inserted. – mvp Feb 11 '13 at 07:49
  • I want to check if NAME, DATE and JOB are the the same, and if they are update HOURS with the new value. If they don't all exist then I would like to insert a new row with those values. Sorry I must not have made it clear enough – Rhys Feb 11 '13 at 08:00
  • Then unique index on `(name,date,job)` should be enough: [SQLFiddle](http://www.sqlfiddle.com/#!5/05b0f/2) – mvp Feb 11 '13 at 08:02
  • I have updated my questions at the bottom to help make what I am after more clear – Rhys Feb 11 '13 at 08:09
  • Doesn't this solve your question yet? Did you check my last [SQLiddle](http://www.sqlfiddle.com/#!5/05b0f/2)? – mvp Feb 11 '13 at 08:11
  • You are bloody genius my friend. Thanks for taking the time to help me out. Very much appreciated. – Rhys Feb 11 '13 at 08:21
  • Don't forget that with `REPLACE INTO` the old row will be deleted before the new row is inserted. At least in MySQL: https://dev.mysql.com/doc/refman/5.7/en/replace.html – tedi Nov 08 '17 at 12:44
3

I think this has been asked here before for 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');
Community
  • 1
  • 1
  • 1
    that should work, out of expirience, be carefull for cascade keys on that table then. They get fired quite often then. – Najzero Feb 11 '13 at 07:19
  • Thanks, but that doesn't seem to work for me. It is just inserting a new line instead of updating. Any suggestions? – Rhys Feb 11 '13 at 07:29
  • @gordatron Still the same, I have update my question to show you. My only though is that the HOURS will be different, as that is the section I want updated. the rest are the same. – Rhys Feb 11 '13 at 07:39
  • Should I have all others set to UNIQUE? – Rhys Feb 11 '13 at 07:43