CODEWITHSUNDEEP
mysqlsqlsql-updateinsert-update
Yasitha
Yasitha

Reputation: 911

How to UPSERT using where clause

I surf through the internet to find and answer to this but I couldn't find a answer.

I want to check four columns before update or insert. I cant use INSERT...ON DUPLICATE KEY UPDATE because it doesn't allow a where clause. so I tried to use following statement that was created by me. 

"SELECT COUNT(id) INTO @count FROM room_prices WHERE (hotel_di = $hotel_id AND room_id = $room_id AND period_id = $periodId AND meal_plan_id = $mealPlanId) "
                . "IF @count > 0 THEN "
                . "UPDATE room_prices SET price = $price WHERE (hotel_di = $hotel_id AND room_id = $room_id AND period_id = $periodId AND meal_plan_id = $mealPlanId) "
                . "ELSE "
                . "INSERT INTO room_prices (hotel_id, room_id, period_id, meal_plan_id, price) VALUES ($hotel_id, $room_id, $periodId, $mealPlanId, $price) "
                . "END IF"

but this one also not working. it gives this error.

The query you submitted is not valid.

is there any way to do this?

Upvotes: 0

Views: 1232

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Start with a unique index on hotel_id, room_id, period_id, meal_plan_id:

CREATE UNIQUE INDEX idx_room_prices_4 on room_prices(hotel_id, room_id, period_id, meal_plan_id)

Then you can use on duplicate key update:

INSERT INTO room_prices(hotel_id, room_id, period_id, meal_plan_id, price) 
    SELECT $hotel_id, $room_id, $periodId, $mealPlanId, $price
    ON DUPLICATE KEY UPDATE price = $price;

Upvotes: 1

Related Questions