July 7, 2014 at 8:13 am
Hi,
I need to update a column with AutoNumber which is depending on some other column.
the output sud be like following..
Obj1Obj2
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0002ijk-001
0002ijk-001
0002ijk-001
0002ijk-001
i want to Update The column Obj2 based on the values of Obj1..
Can anyone suggest me how to do it..
Thanks,
July 7, 2014 at 8:15 am
CherrySqlserver (7/7/2014)
Hi,I need to update a column with AutoNumber which is depending on some other column.
the output sud be like following..
Obj1Obj2
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0002ijk-001
0002ijk-001
0002ijk-001
0002ijk-001
i want to Update The column Obj2 based on the values of Obj1..
Can anyone suggest me how to do it..
Thanks,
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 9, 2014 at 1:47 pm
As Sean indicates, you need to provide better information to get a good answer. From what you posted, we have no clear picture as to what you really mean when you say you "need to update a column with AutoNumber". We could guess that maybe you mean that Obj1 is a field that has some kind of sequence number in it, and you want to take that same exact number and place it into Obj2 at some position other than the beginning of that field, but that presumes an awful lot. It also kind of re-imagines the concept of using AutoNumber, and we can't even be sure that you mean you want a sequence of integers precisely because you specified that something in Obj1 was going to end up in Obj2. As we have no magic genie that can tell us exactly what you meant, you'll have to spell it out and in considerably more detail. You don't need to write a novel, but you need to be as explicit as possible. Sean provided links to articles that provide good guidance on how to post what's needed. We'd love to help, but you haven't exactly told us enough about what you are doing for us to do more than guess, and that's not usually a good idea...
CherrySqlserver (7/7/2014)
Hi,I need to update a column with AutoNumber which is depending on some other column.
the output sud be like following..
Obj1Obj2
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0001ijk-000
0002ijk-001
0002ijk-001
0002ijk-001
0002ijk-001
i want to Update The column Obj2 based on the values of Obj1..
Can anyone suggest me how to do it..
Thanks,
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 11, 2014 at 7:22 am
It would have better if you had provided some more information as the previous posts have suggested.
Anyways, based on the limited information you have provided I think this is what you need
; WITH CTE_TABLE_NAME AS
(
SELECT*, DENSE_RANK() OVER( ORDER BY Obj1 ) AS RN
FROMTABLE_NAME
)
UPDATECTE_TABLE_NAME
SETObj2 = 'ijk-' + RIGHT( '00' + CAST( RN AS VARCHAR(3) ), 3 )
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply