Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

update col numbers

Posted on 2011-03-01
9
Medium Priority
?
906 Views
Last Modified: 2012-05-11
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
Comment
Question by:Tech_Men
  • 5
  • 3
9 Comments
 
LVL 5

Assisted Solution

by:Angelgeo
Angelgeo earned 400 total points
ID: 35006112
update dbo.Products
set ProName=1001+ProName
0
 
LVL 5

Expert Comment

by:Angelgeo
ID: 35006118
update dbo.Products
set ProName=1000+ProName
0
 

Author Comment

by:Tech_Men
ID: 35006137
soryy
what abhot if i have in the ProName :
XXX
YYY
SSS
now how can fix that to :
1001
1002
1003
???
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 5

Expert Comment

by:Angelgeo
ID: 35006152
UPDATE dbo.Products
   SET [ProName] = cast((cast(ProName as numeric)+1000) as varchar)
0
 

Author Comment

by:Tech_Men
ID: 35006174
Error converting data type nvarchar to numeric.
0
 

Author Comment

by:Tech_Men
ID: 35006190
i want to set a numeric from number to number
to this fild
even if the fild are filled whit string
0
 

Author Comment

by:Tech_Men
ID: 35006203
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
 
LVL 22

Accepted Solution

by:
Thomasian earned 1600 total points
ID: 35006253
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
 

Author Closing Comment

by:Tech_Men
ID: 35006428
thanks
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question