Unique Number Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Unique Number Help

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-02-17 : 23:14:09

Hi SQL Gurus,

Here is a situation,

I have sample data like this
P_ID
1234
0000
4567
0000

I am transferring data from flat file to sql server our company database. P_ID is P.K in our db, but there is bunch of P-ID = 0000, I want to update P_ID = 0000 to Unique Number e.g
P_ID
1234
P-0001
4567
P-0002

Please help me out how i can accomplish in sql? Please let me know if my question is not clear very well?

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-17 : 23:27:38

you can use row_number() to generate the running no and then format it to accordingly and update back to the table.


update t
set    P_ID = 'P-' + right('0000' + convert(varchar(4), row_no), 4)
from   (
           select P_ID, row_no = row_number() over (order by P_ID)
           from   yourtable
           where  P_ID = '0000'
       ) t

make sure you don't have any existing P-xxxx P_ID value in the table

KH
[spoiler]Time is always against us[/spoiler]

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-18 : 00:59:41

Hi, Try this ......

CREATE TABLE #temp
(
P_ID varchar(10)
)
go
INSERT #temp
SELECT '1234' union all
SELECT '0000' union all
SELECT '4567' union all
SELECT '0000'
go

UPDATE #temp
SET P_id ='P-'+ right('0000' + convert(varchar(4), number), 4)
FROM
(
SELECT t.p_id,ROW_NUMBER() OVER(ORDER BY t.p_id) number
FROM #temp t
WHERE t.P_ID='0000'
)t
WHERE #temp.P_ID='0000'

--Ranjit

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-18 : 10:22:47

Do it in SELECT statement so that whenever data are added you dont need to update the table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources