| Author |
 Topic  |
|
|
icw
Constraint Violating Yak Guru
378 Posts |
 Posted - 08/17/2012 : 12:38:21
|
Hi I'm trying to update a table of 25,000 rows with a sequential unique number beginning with "P" for product
e.g.
P000001
P000002
P000003
etc
I can start from 1 and just keep going up.
I think I need to declare an Int variable but I am not sure howto do that.
If anyone can help it would really help me out
thanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 08/17/2012 : 12:49:43
|
something like this:
update t1
set YourNewColumn = 'P' + right('000000' + convert(varchar(10),rnum),6)
from YourTable t1
join
(select row_number() over (order by (select 1)) as rnum,* from YourTable) dt
on t1.IdColumn = dt.IdColumn
Too old to Rock'n'Roll too young to die. |
 |
|
|
icw
Constraint Violating Yak Guru
378 Posts |
Posted - 08/18/2012 : 00:39:39
|
Thanks - both replys were helpful.
I've done it!! |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 08/25/2012 : 21:42:47
|
You can do it without having to do 2 table scans. Most people don't know that you can update through a CTE like this...
WITH cte AS
(
SELECT CalculatedProductNumber = 'P' + RIGHT('000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(6)),6),
Product
FROM #SomeTable
)
UPDATE cte
SET Product = CalculatedProductNumber
--Jeff Moden |
Edited by - Jeff Moden on 08/25/2012 21:45:50 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 09/03/2012 : 09:28:27
|
quote:
Originally posted by Jeff Moden
You can do it without having to do 2 table scans. Most people don't know that you can update through a CTE like this...
WITH cte AS
(
SELECT CalculatedProductNumber = 'P' + RIGHT('000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(6)),6),
Product
FROM #SomeTable
)
UPDATE cte
SET Product = CalculatedProductNumber
--Jeff Moden
Yes. It is also possible to delete through a CTE 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 09/03/2012 : 09:33:20
|
quote:
Originally posted by icw
Hi I'm trying to update a table of 25,000 rows with a sequential unique number beginning with "P" for product
e.g.
P000001
P000002
P000003
etc
I can start from 1 and just keep going up.
I think I need to declare an Int variable but I am not sure howto do that.
If anyone can help it would really help me out
thanks in advance
Also read this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3430 Posts |
Posted - 09/03/2012 : 10:13:08
|
quote:
Originally posted by madhivanan
quote:
Originally posted by Jeff Moden
You can do it without having to do 2 table scans. Most people don't know that you can update through a CTE like this...
WITH cte AS
(
SELECT CalculatedProductNumber = 'P' + RIGHT('000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(6)),6),
Product
FROM #SomeTable
)
UPDATE cte
SET Product = CalculatedProductNumber
--Jeff Moden
Yes. It is also possible to delete through a CTE
Madhivanan
Failing to plan is Planning to fail
And merge. Especially merge....
Practically the entirety of uses I have for CTE are due to merges.
Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
| |
Topic |
|
sequential incremental number
