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

[toptomato]

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

[NoEffinWay]

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

[NoEffinWay]

Also, if you are making custom forms, I would think about storing the form data as serialized arrays.

[toptomato]

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.

[criterion9]

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.

[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

[NogDog]

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.

[Dasher]

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.

[toptomato]

ok, thanks for the input