C# SQL: How can I increment an integer value in a database by one on multiple records?

Question

I am looking for the best way to update the value I store on the following structure:

Table: Pages

Fields:

id (int)
bookid (int)
pageorder (int)
filename (string/varchar)

So I have the book order and a page number. I need to insert a page before the page number I have, lets say 25, and update all the other pages to have 1 added to their pageorder value.

Can I do this without pulling a list and cycling threw it running updates, or is that the best way to go about it?

Thanks!

The Evil Greebo · Accepted Answer · 2011-06-10 17:48:11Z

7

declare @newpage int
set @newpage = 25
update pages set pageorder = pageorder +1 where pageorder >= @newpage and bookid = @bookid

something like that?

edited Jun 10, 2011 at 17:48

answered Jun 10, 2011 at 17:40

The Evil Greebo

7,1753 gold badges31 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shane Grant Over a year ago

Looks like it is working, thanks a million! Will accept when I can.

Brook Over a year ago

Probably want to filter by bookid, otherwise that's going to end badly.

The Evil Greebo Over a year ago

Yes I only gave a very rough prototype - you definitely want to add filtering so you don't update EVERY book.

Shane Grant Over a year ago

Ya I figured, but your answer gave me the bit of information that somehow slipped out of my mind.

Brook · Accepted Answer · 2011-06-10 17:51:07Z

2

Strictly with SQL, Like this

update pages 
set pageorder = pageorder + 1
where bookid=@bookid
and pageorder >= @pageorder;

insert into Pages
(id,bookid,pageorder,filename)
values
(@id,@bookid,@pageorder,@filename);

edited Jun 10, 2011 at 17:51

answered Jun 10, 2011 at 17:42

Brook

6,0293 gold badges33 silver badges45 bronze badges

3 Comments

The Evil Greebo Over a year ago

You want to be careful to do your update first, otherwise you'll end up with two page 25's after the insert that'll both become page 26 after the update.

InBetween Over a year ago

Not sure its worthy to downvote. The accepted answer doesnt even filter by bookId :p That can end up even worse.

Brook Over a year ago

I'm assuming the downvote is because initially because I had the insert first. The funny part is that I actually made a mental note to do the update first, then proceeded to type it wrong. Oh well, fixed now.

Collectives™ on Stack Overflow

C# SQL: How can I increment an integer value in a database by one on multiple records?

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related