Renumbering Invoice Nos. through query ?

How can I renumber the following Invoice Nos. through Query.
After sorting by date, the 1st invoice no. to be started with 2006050001
Current Records in table
Before Query
InvNo Date
2006050133 12-MAY-06
2006050134 12-MAY-06
2006050135 12-MAY-06
2006050136 15-MAY-06
2006050137 04-MAY-06
2006050138 03-MAY-06
2006050139 03-MAY-06
2006050140 03-MAY-06
2006050141 03-MAY-06
2006050142 03-MAY-06
2006050143 03-MAY-06
After Query
InvNo Date
2006050001 03-MAY-06
2006050002 03-MAY-06
2006050003 03-MAY-06
2006050004 03-MAY-06
2006050005 03-MAY-06
2006050006 03-MAY-06
2006050007 04-MAY-06
2006050008 12-MAY-06
2006050009 12-MAY-06
2006050010 12-MAY-06
2006050011 15-MAY-06
Any idea please ?
Best Regards,
LuqmanCreate a new table with
Select InvNo ,Date Into NewTable From OriginalTable Order By Date -- I hope
you have more field to use in order by since the date is repeating
Then goto newtable and add a new Identity Field as ID
Now you should have
New Table
ID InvNo Date
1 2006050138 03-MAY-06
2 2006050139 03-MAY-06
3 etc
4
5
Now
you can update your original table by joining with new table and and
formating your new InvNo using ID field.
hope this helps,
erdal
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OjrLclVhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>|||create table invoice_sam(
InvNo varchar(25),
Date smalldatetime)
insert into invoice_sam values('2006050133','12-MAY-06')
insert into invoice_sam values('2006050134','12-MAY-06')
insert into invoice_sam values('2006050135','12-MAY-06')
insert into invoice_sam values('2006050136','15-MAY-06')
insert into invoice_sam values('2006050137','04-MAY-06')
insert into invoice_sam values('2006050138','03-MAY-06')
insert into invoice_sam values('2006050139','03-MAY-06')
insert into invoice_sam values('2006050140','03-MAY-06')
insert into invoice_sam values('2006050141','03-MAY-06')
insert into invoice_sam values('2006050142','03-MAY-06')
insert into invoice_sam values('2006050143','03-MAY-06')
select identity(int,1,1) as sno,
invno,
date
into #step1
from invoice_sam
order by date asc
select substring(invno,1,6) +
case when sno between 1 and 9 then '000' + convert(varchar(5),sno)
when sno between 10 and 99 then '00' + convert(varchar(5),sno)
when sno between 100 and 999 then '0' + convert(varchar(5),sno)
else convert(varchar(5),sno)
End as invno,
date
from #step1
Regards
Sudarshan Selvaraja
"Luqman" wrote:

> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||I hope you want the number to start from 1 for each month ..
You can try this Query
UPDATE table1 SET InvNum =
LEFT(CONVERT(VARCHAR,InvDate,112),6) +
REPLACE
(
STR(
(SELECT
COUNT(*)
FROM
Table1 t2
WHERE
t2.Num1 <= Table1.Num1
AND YEAR(Table1.InvDate) = YEAR(t2.InvDate) AND MONTH(Table1.InvDate) =
MONTH(t2.InvDate)
)
,4),
' ','0')
- Sha Anand
"Luqman" wrote:

> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||A bit simpler, (not requiring a temporary table)
select left(s1.InvNo,6) +
right('0000'+
cast((select count(*)
from invoice_sam s2
where s2.Date < s1.Date
or (s2.Date = s1.Date
and s2.InvNo <= s1.InvNo)) as varchar(4)),4) as
InvNo,
Date
from invoice_sam s1
order by 1

Renumbering an Identity Column

Is there any way to renumber an identity column?
Due to rows being deleted etc., I have identity columns that go 1, 3, 22, 23
etc.
Is there a way to renumber them 1, 2, 3, 4 etc.?
Hi,
1. Take the data out to a temp table
2. Truncate the original table or use DBCC CHECKIDENT with reseed option to
reset the value back to 1
3. Load the data back to the table. Please do not include the identity
column while insertion. This will generate the
values in order .
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
>
|||"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
As Hari showed, yes there is a way.
Remember this violates the rules of database integrity
|||this implies that you are placing meaning on the numbers.
this is bad design.
(For what it's worth)
Greg Jackson
PDX, Oregon
|||It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> this implies that you are placing meaning on the numbers.
> this is bad design.
> (For what it's worth)
>
> Greg Jackson
> PDX, Oregon
>
|||Greg,
Good points.
But then you have to proliferate the new PK values to all child table foreign keys that refer to this table, yes? Sounds like an operation very susceptible to errors.
Thanks,
Dick
-- Greg Linwood wrote: --
It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> this implies that you are placing meaning on the numbers.
> PDX, Oregon
|||Hi Dick.
Yes - proliferating the keys down to any referencing foreign keys is a
requirement, but this is not susceptible to errors as long as it's planned
well.
If there is any maintenance window, this can be performed as part of regular
maintenance. If not, it needs to be performed transactionally and scheduled
regularly enough that this is not a large burden on the system.
Regards,
Greg Linwood
SQL Server MVP
"Dick" <deacdb2@.hotmail.com> wrote in message
news:8F1D7D0A-5669-4A9E-8434-EA2101D3CE9F@.microsoft.com...
> Greg,
> Good points.
> But then you have to proliferate the new PK values to all child table
foreign keys that refer to this table, yes? Sounds like an operation very
susceptible to errors.
> Thanks,
> Dick
>
> -- Greg Linwood wrote: --
> It doesn't necessarily imply that he's placing meaning on the
numbers. He
> might not even have designed the database!
> Compacting ID columns can be an important maintenance task,
particularly to
> avoid overloading ID columns that use smaller int types, such as
smallint.
> Simple example:
> (1) You've got a table that uses smallint for the identity datatype.
(3rd
> party app & you can't change this)
> (2) You have only 1 row, but they identity value is 32766, so you're
running
> out of space - the ID column will only accept one more row. (This
happens in
> cases where you archive data.)
> (3) You need to compact the data so that you can fit more rows in the
table
> by re-assigning keys to smaller values.
> (4) The only solution in this case is to do what Keith is asking..
> This occurs often in apps that use identity, acquire lots of data and
> perform regular archiving, leaving massive gaps in the ID ranges.
It's also[vbcol=seagreen]
> not only common on SQL Server, it happens on all major DBMS.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...

Renumbering an Identity Column

Is there any way to renumber an identity column?
Due to rows being deleted etc., I have identity columns that go 1, 3, 22, 23
etc.
Is there a way to renumber them 1, 2, 3, 4 etc.?Hi,
1. Take the data out to a temp table
2. Truncate the original table or use DBCC CHECKIDENT with reseed option to
reset the value back to 1
3. Load the data back to the table. Please do not include the identity
column while insertion. This will generate the
values in order .
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
>|||"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
As Hari showed, yes there is a way.
Remember this violates the rules of database integrity|||this implies that you are placing meaning on the numbers.
this is bad design.
(For what it's worth)
Greg Jackson
PDX, Oregon|||It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> this implies that you are placing meaning on the numbers.
> this is bad design.
> (For what it's worth)
>
> Greg Jackson
> PDX, Oregon
>|||Greg,
Good points.
But then you have to proliferate the new PK values to all child table foreig
n keys that refer to this table, yes? Sounds like an operation very suscept
ible to errors.
Thanks,
Dick
-- Greg Linwood wrote: --
It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> this implies that you are placing meaning on the numbers.
> PDX, Oregon|||Hi Dick.
Yes - proliferating the keys down to any referencing foreign keys is a
requirement, but this is not susceptible to errors as long as it's planned
well.
If there is any maintenance window, this can be performed as part of regular
maintenance. If not, it needs to be performed transactionally and scheduled
regularly enough that this is not a large burden on the system.
Regards,
Greg Linwood
SQL Server MVP
"Dick" <deacdb2@.hotmail.com> wrote in message
news:8F1D7D0A-5669-4A9E-8434-EA2101D3CE9F@.microsoft.com...
> Greg,
> Good points.
> But then you have to proliferate the new PK values to all child table
foreign keys that refer to this table, yes? Sounds like an operation very
susceptible to errors.
> Thanks,
> Dick
>
> -- Greg Linwood wrote: --
> It doesn't necessarily imply that he's placing meaning on the
numbers. He
> might not even have designed the database!
> Compacting ID columns can be an important maintenance task,
particularly to
> avoid overloading ID columns that use smaller int types, such as
smallint.
> Simple example:
> (1) You've got a table that uses smallint for the identity datatype.
(3rd
> party app & you can't change this)
> (2) You have only 1 row, but they identity value is 32766, so you're
running
> out of space - the ID column will only accept one more row. (This
happens in
> cases where you archive data.)
> (3) You need to compact the data so that you can fit more rows in the
table
> by re-assigning keys to smaller values.
> (4) The only solution in this case is to do what Keith is asking..
> This occurs often in apps that use identity, acquire lots of data and
> perform regular archiving, leaving massive gaps in the ID ranges.
It's also[vbcol=seagreen]
> not only common on SQL Server, it happens on all major DBMS.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...

Renumbering an Identity Column

Is there any way to renumber an identity column?
Due to rows being deleted etc., I have identity columns that go 1, 3, 22, 23
etc.
Is there a way to renumber them 1, 2, 3, 4 etc.?Hi,
1. Take the data out to a temp table
2. Truncate the original table or use DBCC CHECKIDENT with reseed option to
reset the value back to 1
3. Load the data back to the table. Please do not include the identity
column while insertion. This will generate the
values in order .
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
>|||"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
As Hari showed, yes there is a way.
Remember this violates the rules of database integrity|||this implies that you are placing meaning on the numbers.
this is bad design.
(For what it's worth)
Greg Jackson
PDX, Oregon|||It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> this implies that you are placing meaning on the numbers.
> this is bad design.
> (For what it's worth)
>
> Greg Jackson
> PDX, Oregon
>|||Greg
Good points.
But then you have to proliferate the new PK values to all child table foreign keys that refer to this table, yes? Sounds like an operation very susceptible to errors
Thanks
Dic
-- Greg Linwood wrote: --
It doesn't necessarily imply that he's placing meaning on the numbers. H
might not even have designed the database
Compacting ID columns can be an important maintenance task, particularly t
avoid overloading ID columns that use smaller int types, such as smallint
Simple example
(1) You've got a table that uses smallint for the identity datatype. (3r
party app & you can't change this
(2) You have only 1 row, but they identity value is 32766, so you're runnin
out of space - the ID column will only accept one more row. (This happens i
cases where you archive data.
(3) You need to compact the data so that you can fit more rows in the tabl
by re-assigning keys to smaller values
(4) The only solution in this case is to do what Keith is asking.
This occurs often in apps that use identity, acquire lots of data an
perform regular archiving, leaving massive gaps in the ID ranges. It's als
not only common on SQL Server, it happens on all major DBMS
Regards
Greg Linwoo
SQL Server MV
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in messag
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl..
> this implies that you are placing meaning on the numbers
>> this is bad design
>> (For what it's worth
>> Greg Jackso
> PDX, Orego
>>|||Hi Dick.
Yes - proliferating the keys down to any referencing foreign keys is a
requirement, but this is not susceptible to errors as long as it's planned
well.
If there is any maintenance window, this can be performed as part of regular
maintenance. If not, it needs to be performed transactionally and scheduled
regularly enough that this is not a large burden on the system.
Regards,
Greg Linwood
SQL Server MVP
"Dick" <deacdb2@.hotmail.com> wrote in message
news:8F1D7D0A-5669-4A9E-8434-EA2101D3CE9F@.microsoft.com...
> Greg,
> Good points.
> But then you have to proliferate the new PK values to all child table
foreign keys that refer to this table, yes? Sounds like an operation very
susceptible to errors.
> Thanks,
> Dick
>
> -- Greg Linwood wrote: --
> It doesn't necessarily imply that he's placing meaning on the
numbers. He
> might not even have designed the database!
> Compacting ID columns can be an important maintenance task,
particularly to
> avoid overloading ID columns that use smaller int types, such as
smallint.
> Simple example:
> (1) You've got a table that uses smallint for the identity datatype.
(3rd
> party app & you can't change this)
> (2) You have only 1 row, but they identity value is 32766, so you're
running
> out of space - the ID column will only accept one more row. (This
happens in
> cases where you archive data.)
> (3) You need to compact the data so that you can fit more rows in the
table
> by re-assigning keys to smaller values.
> (4) The only solution in this case is to do what Keith is asking..
> This occurs often in apps that use identity, acquire lots of data and
> perform regular archiving, leaving massive gaps in the ID ranges.
It's also
> not only common on SQL Server, it happens on all major DBMS.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> > this implies that you are placing meaning on the numbers.
> >> this is bad design.
> >> (For what it's worth)
> >> Greg Jackson
> > PDX, Oregon
> >>

Renumber column....

Hi everyone.. kindly help me out.. im a newbie by the way.. ihave this fields in my table... uid, linenum, name... my problem is everytime i wnt to delete a record i want the "line: field to be renumbered.. for exmple...
record 1:
uid=1, line=1, name=Bob
record 2:
uid=2, line=2, name=Greg
record 3:
uid=3, line=3, name=Don...

now, i deleted record 2... whatt should happen is line must be renumbered..
record 1:
uid=1, line=1, name=Bob
record 2:
uid=3, line=2, name=Don

im thingking that i should create a trigger... but how will be my approach in SQL script.. help.. pls...

Try this. It will subtract one from the current line for each record in the deleted table with a line number less than it. So, if the table starts with lines 1-10 and we delete lines 3,5, and 7. Lines 10, 9, and 8 should be decreased by 3; 6 by 2; and 4 by 1.

Drop Table BensTable go Create Table BensTable( uid int not null, linenum int not null, name varchar(100) null ) insert BensTable values (1, 1, 'Fred' ) insert BensTable values (2, 2, 'Barney' ) insert BensTable values (3, 3, 'Jane' ) insert BensTable values (4, 4, 'Wilma' ) insert BensTable values (5, 5, 'George' ) insert BensTable values (6, 6, 'Betty' ) insert BensTable values (7, 7, 'Astro' ) insert BensTable values (8, 8, 'Pebbles' ) insert BensTable values (9, 9, 'BamBam' ) insert BensTable values (10, 10, 'Dino' ) go Create Trigger Renumber on BensTable for Delete AS Begin Update B set linenum = linenum - ( Select Count(*) From deleted as d Where d.linenum < B.linenum ) From BensTable as b End go delete From BensTable where linenum in ( 3, 5, 7 ) select * from BensTable order by linenum

Triggers have 2 pseudo-tables available within them, called inserted and deleted. These can be used, like other tables, in queries to perform checks or other operations.

|||thank you very much...!!!!|||another question what if this is the scenario...

i have a table named tbl1 fields are... uid, name.. and another table named tbl2 fields are tbl1uid,hobbies, line
where tbl1uid foreign key from tbl1...

im going to delete a record.. on the tbl2 table... but the records that must be renumbered are records that has the uid of tbl1...

thanks for the help..|||

It is really bad idea to keep the line-number in the table itself. You can get the line-number while fetching the record. if you use SQL Server 2005 then you can utilize the ROW_NUMBER feature.

Which version of SQL Server are you using?

|||im using SQL server 2005 express edition...|||

If you use SQL Server 2005, you need not to keep a separate column. It is unnessary, it will cause additional overhead on every insert / update / delete and it will consume reasonable memory also,

Use the following logic to number your row,

Code Snippet

Create Table #data (

[uid] Varchar(100) ,

[name] Varchar(100)

);

Insert Into #data Values('1','Bob');

Insert Into #data Values('2','Greg');

Insert Into #data Values('3','Don');

Select uid,name,row_number() Over(order By uid) from #data

delete from #data Where uid=2

Select uid,name,row_number() Over(order By uid) from #data

|||

If your intention is keep the line number on the table itself then the following query help you.

Code Snippet

Create Table data (

[uid] Varchar(100) ,

[name] Varchar(100) ,

[line] int

);

Go

Create trigger data_renumber

on data for insert, delete

as

begin

;WithCTE

as

(

Select *, row_number() Over(order By uid) newline from data

)

Update CTE Set line = newline;

end

Go

Insert Into data(uid,name) Values('1','Bob');

Insert Into data(uid,name) Values('2','Greg');

Insert Into data(uid,name) Values('3','Don');

Go

Select *from data

delete from data Where uid=2

Select * from data

|||um.. sorry... but what if i'll implment the renumbering using a stored procedure? how will it be.. thanks..

renumber a column...

Hi. How can I renumber a column in my database? This is what we use to identify a particular record:

CASEID PARTYID
11 1
11 2
23 1
23 2
23 3
I want to stop renumering for each entry. How can I update the entire so each PARTYID is renumbered. I want to make this the primary key for the table. Thanks for your help!CREATE TABLE myTable99(CASEID int, PARTYID int)
GO

INSERT myTable99(CASEID, PARTYID)
SELECT 11, 1 UNION ALL
SELECT 11, 2 UNION ALL
SELECT 23, 1 UNION ALL
SELECT 23, 2 UNION ALL
SELECT 23, 3
GO

SELECT * FROM myTable99
GO

ALTER TABLE myTable99 ADD newPARTYID int IDENTITY(1,1) PRIMARY KEY
GO

SELECT * FROM myTable99
GO

EXEC sp_rename 'myTable99.PARTYID', 'PARTYID_Old', 'COLUMN'
EXEC sp_rename 'myTable99.newPARTYID', 'PARTYID', 'COLUMN'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO|||Thanks! Worked like a charm!