Update query when exists, but insert when not

Question

I need an update query with a WHERE statement, but if the record does not exist it should insert the data.

e.g. UPDATE table SET this = 'that', that = 'this' WHERE this = 'this' AND that = 'that'

I don't have an unique value, I have 2 columns that can't occur 2 times or more. Let me give you an example:

I've got a table:

Column1 | Column2 | Column3
---------------------------
1       | 2       | 500
1       | 3       | 500 This is OK because column2 is other than above
2       | 2       | 500 ALSO OK because column1 is other than above!
1       | 2       | 500 NOT OK! Already exists -> need update for Column3! needs to be 1000 now!

As you can see in the table, the two columns can occur more than once!

Is there a function for this? Or should I first check if there is a record? I do not have an Unique key, so I can't use this.

Take a look at the mysql documentation: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — arkascha, Nov 23 '15 at 12:58
this is logical question please refer my ans and if you any doubt please inform me in comment — Darshan Dave, Nov 23 '15 at 13:12
@Dennis First of all: I did not downvote anything, so please do not point fingers. I only try to help. Thanks. About my comment: I have the impression (I might be wrong there, sure), that the documented feature is _exactly_ what you are looking for: It allows to run an `INSERT` statement on a table which will automatically does an `UPDATE` instead if a matching entry already exists. All you have to do is define correct indexes. The documentation gives examples, so there is nothing more to say. I suggest you try to understand that feature before judging. — arkascha, Nov 23 '15 at 13:19
this is good logical question n i found somebody putted downvote so i rise up again — Darshan Dave, Nov 23 '15 at 13:21

score 0 · Answer 1 · answered Nov 23 '15 at 13:11

0

Do Like this First fire select query

select column_name from table where condition

and use mysqli_num_rows function to count fetch data row ,here if condition will wrong the dataset number of row will be zero and if data has fetched then dataset row count will one. So what you have to do now is like show below demo code

$no_rows=mysqli_num_rows(..);
if($no_rows==1)
{
  update query;
}
else
{
 insert query;
}

it means that if row count is 1 it will update it otherwise it will insert data.

answered Nov 23 '15 at 13:11

Darshan Dave

645
2
9
32

Yes, that's what I said in the question. But I can't use `ON DUPLICATE KEY UPDATE`, because I don't have an unique field? – Refilon Nov 23 '15 at 13:14
if possible can you share your query and table – Darshan Dave Nov 23 '15 at 13:16
ok what kind of data are you want to update or insert in table means is it char or number ? – Darshan Dave Nov 23 '15 at 13:19

score 0 · Answer 2 · answered Nov 23 '15 at 13:57

You can use insert . . . on duplicate key update. Something like this:

insert into table(this, that)
    select 'this', 'that'
    on duplicate key update this = values(this), that = values(that);

For this to work, you need a unique index/constraint on the columns that specify "that a row already exists". Your question is a bit vague on the details. Hopefully this will put you on the right track.

Update query when exists, but insert when not

2 Answers2