Update if exist, insert if not exist [duplicate]

Question

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.

Answer 1

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.