Solved

update col numbers

Posted on 2011-03-01
9
887 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 100 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 400 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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 …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 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