Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 908
  • Last Modified:

update col numbers

hi there ,
i have this col that i want to update
proName (nvarhcar type)
1
2
3
4
i want to update its to :
1001
1002
1003
1004
i try to do this :
update dbo.Products set ProName=1001+1
but its not working
how can i fix that ?

0
Tech_Men
Asked:
Tech_Men
  • 5
  • 3
2 Solutions
 
Kakhaber SiradzeCommented:
update dbo.Products
set ProName=1001+ProName
0
 
Kakhaber SiradzeCommented:
update dbo.Products
set ProName=1000+ProName
0
 
Tech_MenAuthor Commented:
soryy
what abhot if i have in the ProName :
XXX
YYY
SSS
now how can fix that to :
1001
1002
1003
???
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kakhaber SiradzeCommented:
UPDATE dbo.Products
   SET [ProName] = cast((cast(ProName as numeric)+1000) as varchar)
0
 
Tech_MenAuthor Commented:
Error converting data type nvarchar to numeric.
0
 
Tech_MenAuthor Commented:
i want to set a numeric from number to number
to this fild
even if the fild are filled whit string
0
 
Tech_MenAuthor Commented:
to make a runing number to all rows in the table
if i have 10 rows and i want if to strat from the number 5000
its end in the last row 5009
0
 
ThomasianCommented:
Change 1000 to the start number - 1, ID to the field name you want the ProName to be sorted on

UPDATE P
SET ProName = rn + 1000
FROM (SELECT ProName, ROW_NUMBER() OVER (ORDER BY ID) rn
        FROM Products
     ) P

Open in new window


Test script
DECLARE @Products table (ID int identity(1,1), ProName nvarchar(100))

INSERT INTO @Products
SELECT 'XXX'
UNION ALL SELECT '1242'
UNION ALL SELECT 'SSF3'
UNION ALL SELECT 'SDFf'

SELECT * FROM @Products 
/*
ID	ProName
1	XXX
2	1242
3	SSF3
4	SDFf
*/

UPDATE P
SET ProName = rn + 1000
FROM (SELECT ProName, ROW_NUMBER() OVER (ORDER BY ID) rn
        FROM @Products
     ) P
     
SELECT * FROM @Products
/* After update
ID	ProName
1	1001
2	1002
3	1003
4	1004
*/

Open in new window

0
 
Tech_MenAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now