www.webdeveloper.com
Results 1 to 9 of 9

Thread: I have column of unique integers, problems trying to update, re-order in a loop....

  1. #1
    Join Date
    Mar 2010
    Posts
    281

    I have column of unique integers, problems trying to update, re-order in a loop....

    Here's a puzzler,...maybe

    This is from a CMS that i'm building, i've simplified here.

    +----------+------------------+
    | manualid | listtext |
    +----------+------------------+
    | 1 | here some text 1 |
    | 2 | here some text 2 |
    | 3 | here some text 3 |
    | 4 | here some text 4 |
    | 5 | here some text 5 |
    +----------+------------------+

    this table displays in a form, allowing the user to change the values.
    the purpose of the manualid field is so the user can re-order the list however
    they want.
    (when displaying on a webpage the query reads '......Order by manualid'),

    i've set the manualid to UNIQUE.

    the problem is that when updating via a for loop, for example,
    if the user sets manualid 1 to 2, and visa versa 2 to 1,
    the update query fails right off because manuald id 2 already exists.

    can someone recommend a way to do this?

    is there a way to write an multiple updates in 1 query, like there is for insert?
    ex.
    insert into table (col1, col2 ) values (data1a, data2a), (data1b, data 2b), (data3a, data3b) ..?

    OR, should i write to a new table, drop the old one then rename the new one to what the deleted old one was??, that seems like a drastic solution

    thanks for any help on this

  2. #2
    Join Date
    Feb 2012
    Location
    Tallahassee, FL
    Posts
    280
    The issue is that you are using an insert query instead of update. You need something like:
    Code:
    UPDATE table SET col1 = field1 WHERE unique = unique

  3. #3
    Join Date
    Feb 2012
    Location
    Tallahassee, FL
    Posts
    280
    Also, if you are making custom forms, I would think about storing the form data as serialized arrays.

  4. #4
    Join Date
    Mar 2010
    Posts
    281
    No I am using update. it's hard sometime to write these posts clearly.

    Problem is, several separate queries are made (or attempted) one after the other via the loop. but because the manual id field is set to UNIQUE (as it probably should be),
    the very first update query fails because that id number is already present in another row.

    so i was asking if several rows could be updated at once, similar to insert as a possible solution to avoid this

    thanks for answering my post.

  5. #5
    Join Date
    Jan 2009
    Posts
    3,346
    Quote Originally Posted by toptomato View Post
    No I am using update. it's hard sometime to write these posts clearly.

    Problem is, several separate queries are made (or attempted) one after the other via the loop. but because the manual id field is set to UNIQUE (as it probably should be),
    the very first update query fails because that id number is already present in another row.

    so i was asking if several rows could be updated at once, similar to insert as a possible solution to avoid this

    thanks for answering my post.
    Can you do a transaction rather than executing each statement one at a time? It may not be a good idea to make your ordering field a simple unique, you might find that you want different types of things grouped together and ordered. If that happens to be the case you'll probably need a complex unique constraint using 2 or more fields.

  6. #6
    Join Date
    Mar 2010
    Posts
    281
    thanks but i don't know what you mean by 'transaction'?, or complex unique constraint using 2 or more fields. can you point me toward an example?, i love learning this stuff. i'm still kind of newb. or, semi-intermediate newb ;-).

    i did accomplish though by writing to a new table, deleting the old table then renaming the new table to the old table. though i'm sure this can't be considered a 'best practice'!

    also, this seems this type of functionality would be a fairly common thing to do, especially with a cms type thing

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    18,921
    I might consider not making the sort field unique. While logically it may make sense, from a data integrity respect it really does not mean much (if two rows have the same value, they'll still sort the same with respect to all other fields -- it's just undefined how they'll sort in relation to each other). Therefore, you could make the enforcement of any uniqueness a responsibility of the application code instead of the DBMS.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  8. #8
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    In my home brew CMS I order the articles by sticky, priority, and articleID. Sticky stay at the top until unstickied, and priority higher than one will move them to the bottom of the stack which is delineated by month. But it could be day, or week as well.

  9. #9
    Join Date
    Mar 2010
    Posts
    281
    ok, thanks for the input

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles