I have two update statements.
the first increments a col value by setting it to its value + 1. it works OK
the second updates a record on duplicate key.
trouble is, if the page is refreshed, the set statement increments the col value and the update on duplicate key doesn't. ( I know that's how they should work )
But, if the page is refreshed, the value of the (not) updated record will stay at say '03' and the value+1 will keep going up with a page refresh each time it is refreshed.
How do I facilitate the value+1 statement but prevent it from doing so siply by refreshing the page?
here's my two statements
set display_sequence = display_sequence + 1
where business_id = ?
and content_category = 'Menus'
and menu_name = 'Breakfast'
and live_from = '2010-10-29'
and course = 'Footer notes'
and dish = 'Footer notes'
insert into dishes
( business_id, content_category, menu_name, live_from, course, dish, description, price, display_sequence)
values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )
on duplicate key
update business_id = ?
, content_category = ?
, menu_name = ?
, live_from = ?
, course = ?
, dish = ?
, description = ?
, price = ?
, display_sequence = ?
Apologies for the ? placeholders but I don't think their presence will hamper an answer. They simple replace the values that would be inserted. it's a perl thing
The two statement are performed inside a transaction
I am adding a new dish to the menu with the on duplicate update statement and I am trying to increment the sequence number of the footer notes to ensure they always display last.