6

Possible Duplicate:
How do I update if exists, insert if not (aka upsert or merge) in MySQL?

I have program that can do batch updating price for booking engine.

example user input for to do update batch the price is like this

$dateStart = '2012-12-01';
$dataEnd = '2012-12-31';
$hotelId = 1;
$price = 300000;

And database is like this

id | date       | hotelId | price
1  | 2012-12-01 | 1       | 100000

How should the MySQL Query, to do batch update and insert with condition if date and hotelId is already exist then do update, but if not exist do insert?

I understand if doing looping day by day and checking if data exist or not in PHP will be really spend much memory. 

for($i=1; $i<=31; $i++){...}

I'm looking solution how it can be done with single/any mysql query that can save computer resource.

Community
  • 1
  • 1
GusDeCooL
  • 5,639
  • 17
  • 68
  • 102

1 Answers1

7

Use the INSERT ... ON DUPLICATE KEY UPDATE ... statement.

It will require that you have a UNIQUE or PRIMARY compound index on the date and hotelId columns so that the DUPLICATE KEY condition is triggered.

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • How to create unique compound index? – GusDeCooL Dec 27 '12 at 17:13
  • 1
    @GusDeCooL - Simple: `ALTER TABLE table ADD UNIQUE KEY (date, hotelId)`. See the [docs on `ALTER TABLE` statement](http://dev.mysql.com/doc/refman/5.5/en/alter-table.html). – Ted Hopp Dec 27 '12 at 17:16