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
(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?
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
The issue is that you are using an insert query instead of update. You need something like:
UPDATE table SET col1 = field1 WHERE unique = unique
Also, if you are making custom forms, I would think about storing the form data as serialized arrays.
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.
Originally Posted by toptomato
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
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
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.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)