SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    trouble with update of table

    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
    Code MySQL:
    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
    Code MySQL:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    So what would the solution be please?
    you really need me to say it??

    patient: "doctor, it hurts when i do this"

    doctor: "so, don't do that"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

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

    bazz

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    So, is there a methodology that would achieve the same effect without being clunky?
    auto_increment?

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



    p.s. whenever you do this --
    Code:
    UNIQUE KEY ( a,b,c,d,e )
    then this is redundant --
    Code:
    KEY ( a,b,c,d )
    because it can use the UNIQUE index for this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    Of course the auto_increment PK is the col to use.


    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    me overthinking it??
    possibly...

    ... or not thinking it through completely

    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, thanks. I got it


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •