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