set sequence = sequence + 1
where sequence > 19
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?
No, it's one query. Dan's suggestion is exactly what you are looking for, viz:
SET sequence = sequence + 1
WHERE sequence > 19
INSERT INTO your_table
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.)
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.