Solved

update col numbers

Posted on 2011-03-01
9
883 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
 
LVL 5

Expert Comment

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

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query - which index being used? 2 48
Sql Permission 6 52
How to simplify my SQL statement? 14 50
Getting max record but maybe not use Group BY 2 16
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…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now