Specific table update

Question

Post reply

Specific table update

branovuk

SSCommitted

Points: 1774
More actions
May 27, 2014 at 2:50 pm

#396820

Hi, I have next:
Select ObjectID, ObjectNumber
FROM Objects
WHERE ObjectID IN
(
9712
,9773
,9785
,9790
)
ORDER BY ObjectID
ObjectID___ObjectNumber
9712______2008/ad
9773______2008/fd
9785______2008/effc
9790______2008/fvf
I need to update table in order to have next:
ObjectID___ObjectNumber
9712______2008/1449
9773______2008/1450
9785______2008/1451
9790______2008/1452
This is just example, I have 10000 rows, starting 2008/1449.
Any ideas?
Thanks
Brano

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

where did the 1449 come from? is it just your arbitrary starting point?

it doesn't exist or seem to be calculate-able from what you posted.

is the 2008/ portion going to come from the existing ObjectNumber, or is that an arbitrary value?

this is my best guess: it gives you the values you want based on the limited sample, so you could update the OriginalObjecrtNumber with the NewObjectNumber

/*

NewObjectNumber RW ObjectID ObjectNumber

--------------------------------------- -------------------- -------- ------------

2008/1449 1 9712 2008/ad

2008/1450 2 9773 2008/fd

2008/1451 3 9785 2008/effc

2008/1452 4 9790 2008/fvf

*/

;WITH MyCTE([ObjectID],[ObjectNumber])

AS

(

SELECT '9712','2008/ad' UNION ALL

SELECT '9773','2008/fd' UNION ALL

SELECT '9785','2008/effc' UNION ALL

SELECT '9790','2008/fvf'

),DataWithRowNum

AS

(

SELECT

ROW_NUMBER() OVER (ORDER BY [ObjectID]) AS RW, *

FROM MyCTE)

SELECT LEFT([ObjectNumber],CHARINDEX('/',[ObjectNumber])) + CONVERT(VARCHAR,RW + 1448) AS NewObjectNumber,* FROM DataWithRowNum;

branovuk (5/27/2014)
Hi, I have next:
Select ObjectID, ObjectNumber
FROM Objects
WHERE ObjectID IN
(
9712
,9773
,9785
,9790
)
ORDER BY ObjectID
ObjectID___ObjectNumber
9712______2008/ad
9773______2008/fd
9785______2008/effc
9790______2008/fvf
I need to update table in order to have next:
ObjectID___ObjectNumber
9712______2008/1449
9773______2008/1450
9785______2008/1451
9790______2008/1452
This is just example, I have 10000 rows, starting 2008/1449.
Any ideas?
Thanks
Brano

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dawn W Ten Centuries Points: 1010 More actions · Answer 2

Is objects a user table or do you mean sys.objects (which you don't want to 'update')? Where are you getting the values for object number? What is the business reason you want to do this update? Maybe we can suggest a better way...

branovuk SSCommitted Points: 1774 More actions · Answer 3

Thanks on answers.

Objects is user table, 2008/ portion is arbitrary,

as well as 1449 is arbitrary starting point.

I am not sure if I can use this, since I have 10000 rows.

Thanks,

Brano