CODEWITHSUNDEEP
c#sql
Shane Grant
Shane Grant

Reputation: 2654

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

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

Table: Pages

Fields:

  1. id (int)
  2. bookid (int)
  3. pageorder (int)
  4. 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!

Upvotes: 1

Views: 2404

Answers (2)

Brook
Brook

Reputation: 6019

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

Upvotes: 2

The Evil Greebo
The Evil Greebo

Reputation: 7138

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

something like that?

Upvotes: 7

Related Questions