How to Update if the data exist else insert the new data (multiple rows)

I need to create a insert and update statement, when today date is not in the database it will insert else it will update the QTY (from excel 开发者_Python百科[this part I have done]) get from today.

But, there have a lots of row need to be insert and update.

1) it will check for the last 4 days in database, if there doesn't include today, it will just insert the data for today and update the last 3 days data. in the other hand, if there contain today it will just update.

P.S: I had try to use INSERT... ON DUPLICATE KEY UPDATE but it only 1 row affected.

If else statement , when i used this it only insert one row of data then the rest it just doing update.

Can give me some advise or example.

suppose you bulk copy your data from excel to a temporary table tbl and your actual table is tbl1 then do something like this

   begin transaction;
 if not exists(select * from tbl(updlock holdlock) where...)
begin
   insert into tbl1...
else
begin
   update tbl1...
end
commit;

What language are you using to do this? I have done something similar in Ruby before. I would make the column (Date in your case) unique at the database level then simply try inserting each record. When I get an exception thrown because the Date is not unique I would then proceed to update the QTY.

I found this article on mysql which says it supports multiple insert. http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

That statement is identical to the following two statements:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO table (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

So if we want to edit straight, we could do something like this.

INSERT INTO table (uniquekey,data) VALUES (1,2),(4,5)
      ON DUPLICATE KEY UPDATE data=VALUES(data);