Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

update col numbers

Posted on 2011-03-01
9
890 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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