Two update statements causing diffs, on page refresh


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 :slight_smile: )

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

update dishes
    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 :slight_smile:

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.


This is something you would need to do in perl, not MySQL. You could store a flag inside the session and expire it when the first transaction occurs. When someone refreshes page redirect them back to the beginning form if no session flag exists.

ah great.

Thank you oddz. I had hoped there was a mysql way but I’ll do it in perl.