Best way to update multiple rows without multiple queries?

[Guest]

Maybe it's because it's Monday but here's my question...

I have a table that contains a sequence column. When I add a new record I sometimes want to insert it between some existing records. So say I want to insert a record with a sequence of 20 and push the records with sequences of 20 thru 200 up one. How else can I update these records without doing 180 queries inside of a loop. It works like this but for obvious reasons I'd like to avoid doing it this way.

[Dan_Bracuk · Accepted solution]

update yourtable

set sequence = sequence + 1

where sequence > 19

[Guest]

That's what I'm doing now. In the example I have to add one sequence value to records 20 through 180, so that's 160 updates unless there's another way to do it?

[TLC-IT]

No, it's one query.  Dan's suggestion is exactly what you are looking for, viz:

<cftransaction>

     <cfquery>

          UPDATE your_table

               SET sequence = sequence + 1

               WHERE sequence > 19

     </cfquery>

     <cfquery>

          INSERT INTO your_table

               (SEQUENCE, ...)

          VALUES

               (19, ...)

     </cfquery>

</cftransaction>

This will, in the auspices of one "all-or-nothing" transaction, first update all rows having a sequence-number greater than 19 to increment their sequence numbers, then insert a new row having a sequence-number of 19.  If and only if both operations succeed, the changes will become permanent.

As an aside, I normally avoid using hard-coded sequence numbers for precisely the same reason that I always numbered lines in BASIC programs, all those years ago (long before Bill Gates made a travesty of that noble language ) in increments of 10 or 100.  It's much easier to ORDER BY some other criteria and then, "if the rows needest be numbered, number them as you print them."  This avoids the costly operation of bumping the numbers in potentially thousands of rows, and of doing so in a transaction.

(BASIC?  Yeah, BASIC as in teletypes and paper-tape readers.  No, I wouldn't go back, but I sure am glad I was there... when computing was both esoteric and fun as it has never been since.)

[Guest]

Your right, Dan's answer was correct. At first look it seemed the same that I had, tried it and worked great, so thanks Dan.

I do need to use sequenced values and usually there won't be more than 100-200 records to update, but I understand what you mean.