如果数据存在则如何更新否则插入新数据（多行）

Question

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

Answer 1

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.