Trouble with update of table

Databases
1

Hi,

my table and query are as below.

I am trying to increment the sequence number but I get a failure because of a duplicate key error. I guess it is incrementing a 13 (say), to a 15, where it hasn;t yet incremented 15 to 17.

So what would the solution be please?

create table statement


CREATE TABLE dishes (
  id bigint(20) NOT NULL auto_increment,
  business_id int(11) NOT NULL,
  content_category varchar(32) collate utf8_unicode_ci NOT NULL default 'Menus',
  menu_name varchar(64) collate utf8_unicode_ci NOT NULL,
  live_from date NOT NULL,
  course varchar(32) collate utf8_unicode_ci NOT NULL,
  dish varchar(255) collate utf8_unicode_ci NOT NULL default 't',
  description text collate utf8_unicode_ci,
  price varchar(64) collate utf8_unicode_ci default '0.00',
  display_sequence int(2) unsigned zerofill NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY business_id_2 (business_id,content_category,menu_name,live_from,display_sequence),
  KEY business_id (business_id,content_category,menu_name,live_from)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

here’s the query


update dishes
     set display_sequence = display_sequence + 2 
 where business_id = 123
    and content_category = 'Menus'
    and menu_name = 'whatsit'
    and live_from = '2010-11-01'
    and display_sequence >= 05

bazz

2

you really need me to say it??

patient: “doctor, it hurts when i do this”

doctor: “so, don’t do that”

:smiley:

3

lol.

OK so maybe I didn’t then understand how mysql would actually perform the update. or maybe I didn’t think it through long enough. :rolleyes:

So, is there a methodology that would achieve the same effect without being clunky?

bazz

4

auto_increment?

see, you did not explain what that effect should be…

:slight_smile:

p.s. whenever you do this –

UNIQUE KEY ( a,b,c,d,e )

then this is redundant –

KEY ( a,b,c,d )

because it can use the UNIQUE index for this

5

Thanks rudy. I did not know that one index would suffice in this case.

My brain must be mush today. Of course the auto_increment PK is the col to use. Quite why I have tried to use the composite foreign key is anybody’s guess.

bazz

6

:slight_smile:

best way to target an individual row, nay?

i’m just curious about why you’re incrementing by 2, and how you planned (unsuccessfully, as it turned out) to avoid collisions

7

OK, it’s a table containing the dishes on a restaurant menu. When the owner views the dishes via the cms, he/she may want to add a couple more dishes to the menu. But they may also need to preserve the display sequence. So; if a five itme menu is to become one of 7 items and the two new dishes are to be #3 and #4 in the menu, I need to increment/increase, the display sequence number for those dishes with a seq_no > 3.

However, as I send the (above), query to do this - even if I get around the dupe issue - I wonder what will happen when seq_no 3 has been upped to 5. so (imaging this as some sort of loop), item(s) 5 (possibly two of them now), will be increased to 7. I shall experiment but I want to avoid an endless loop where they become increased infinitely.

me overthinking it??

bazz

8

possibly…

… or not thinking it through completely :wink:

i’m not sure if you and i have had this sequencing conversation before, but i know i’ve posted on it several times

the way i handle sequencing is with the use of FLOAT rather than INTEGER

there was a time when i used INTEGER but assigned values in increments of 100, so that if we had your 5-item menu, like this –

100 soup
200 entree
300 salad
400 main
500 afters

if you now wanted to add 2 more items to this, and specifically add them in between salad and main, the easiest way is to add them with values for the sequence column such as 335 and 365

notice something? no existing rows need to be updated!

eventually, though, as some people pointed out, you might have to re-sequence everything, in case you’ve stuffed more than 99 new items in between two existing ones

(so, said i, mainly to myself, why not assign the initial values in increments of 1000? that’s gonna guarantee a long time in between re-sequences…)

eventually i drank the koolaid and decided to use FLOAT

that way, you can stuff, for all practical purposes, as many new rows in between two existing rows as your heart desires, and never need to re-sequence

neat, eh?

9

Thanks, rudy.

I see the logic with that approach. But I don’t ‘get’ the necessity to use FLOAT instead of INT. Or even the value of doing so. Would you mind explaining a bit further please?

bazz

10

well, if we start with your sequence numbers of 1, 2, 3…

did you understand my explanation with values 100, 200, 300… ?

the use of FLOAT is exactly the same, except with FLOAT values, and you can keep splitting the distance between any two existing values indefinitely

i’m not sure what else i can explain…

11

OK, thanks. I got it :slight_smile: