Skip to main content
A
Anonymous
August 3, 2009
Answered

Best way to update multiple rows without multiple queries?

  • August 3, 2009
  • 2 replies
  • 1188 views

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.

This topic has been closed for replies.
Correct answer Dan_Bracuk

update yourtable

set sequence = sequence + 1

where sequence > 19

2 replies

T
TLC-IT
Inspiring
August 4, 2009

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.)

A
Anonymous
August 4, 2009

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.

D
Dan_BracukCorrect answer
Inspiring
August 3, 2009

update yourtable

set sequence = sequence + 1

where sequence > 19

A
Anonymous
August 4, 2009

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?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices