SQL: How to update or insert if doesn't exist?

Question

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

yes it is possible, even by severall approaches, my best bet would be UNIQUE() constraint. — Najzero
– Najzero, Commented Feb 11, 2013 at 7:16

mvp · Accepted Answer · 2013-02-11 08:14:17Z

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.

edited Feb 11, 2013 at 8:14

answered Feb 11, 2013 at 7:20

mvp

118k15 gold badges132 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Rhys Over a year ago

So, which INDEX's do I set to unique? HOURS is the one that I want to change if the others exist.

mvp Over a year ago

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.

Rhys Over a year ago

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

mvp Over a year ago

Then unique index on (name,date,job) should be enough: SQLFiddle

Rhys Over a year ago

I have updated my questions at the bottom to help make what I am after more clear

|

Community · Accepted Answer · 2017-05-23 12:21:59Z

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

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Feb 11, 2013 at 7:18

user359135

4 Comments

Najzero Over a year ago

that should work, out of expirience, be carefull for cascade keys on that table then. They get fired quite often then.

Rhys Over a year ago

Thanks, but that doesn't seem to work for me. It is just inserting a new line instead of updating. Any suggestions?

Rhys Over a year ago

@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 Over a year ago

Should I have all others set to UNIQUE?

Collectives™ on Stack Overflow

SQL: How to update or insert if doesn't exist?

2 Answers 2

8 Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Linked

Related