i want update the 2nd column based on first column:

Question

Post reply

i want update the 2nd column based on first column:

DBA.k

Ten Centuries

Points: 1105
More actions
March 21, 2013 at 3:49 am

#275410

hear i have id column based on that getting one more column as mid and data as belo:
id
100
200
300
400
500
Expected output:
id Mid
100 NULL
200 100
300 200
400 300
500 400

Viewing 14 posts - 1 through 13 (of 13 total)

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

What logic are you using to determine the value of the second column?

I can guess of a few - my guesses can be wrong though.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

No chance you are using SQL Server 2012? Because this would be easy with the new lag/lead functions.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Agreed that if using 2012 there are easier options. Also would be better if we knew what your requirements are. I took a shot in the dark.

;with Data(ID)

as

(

select 100 union all

select 200 union all

select 300 union all

select 400 union all

select 500

)

select *

from Data d

outer apply

(

select top 1 ID from Data d2 where d2.ID < d.ID order by ID desc

) x

_______________________________________________________________

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/

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

This is a classic problem and the classic solution, which runs on all versions of SQL Server, turns out to be quite fast.

--===== Create the test data.

-- This is NOT a part of the solution.

-- We're just building the test data table

-- to look as expected.

SELECT ID = Number

INTO #YourTable

FROM master.dbo.spt_values

WHERE Type = 'P'

AND Number %100 = 0

;

--===== Add the expected clustered index.

-- This is also NOT a part of the solution.

-- We're just building the test data table

-- to look as expected.

ALTER TABLE #YourTable

ADD PRIMARY KEY CLUSTERED (ID)

;

--===== This is the classic solution that will work on all versions of

-- SQL Server and is very fast in the presence of the correct index.

SELECT ID, MID = (SELECT TOP 1 ID FROM #YourTable t2 WHERE t2.ID < t1.ID ORDER BY t1.ID DESC)

FROM #YourTable t1

;

{EDIT} If you compare the code above to Sean's good code, it also demonstrates that APPLY isn't much more than a sophisticated correlated sub-query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

carlosaamaral Old Hand Points: 325 More actions · Answer 5

Logic ?

MID=(CASE WHEN ID-100=0 THEN NULL

ELSE ID-100 END

)

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

carlosaamaral (3/21/2013)
Logic ?

MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)

That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.

_______________________________________________________________

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/

carlosaamaral Old Hand Points: 325 More actions · Answer 7

Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)

That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.

ok ... but sometimes the simple, we do not see ... Without data, it really is very, very complicated ...

Stuart Davies SSCoach Points: 19505 More actions · Answer 8

carlosaamaral (3/21/2013)
Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)

That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.
ok ... but sometimes the simple, we do not see ... Without data, it really is very, very complicated ...

Exactly - which is why most of my answers to questions don't involved code. We don't see the logic (business or otherwise) behind the question - only the desire to see the result.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

carlosaamaral (3/21/2013)
Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)

That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.
ok ... but sometimes the simple, we do not see ... Without data, it really is very, very complicated ...

Absolutely agreed. It's just that some of us have been doing this so long that we've learned to anticipate various data "possibilites" and to bulletproof the code a bit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286573 More actions · Answer 10

carlosaamaral (3/21/2013)
Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)

That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.
ok ... but sometimes the simple, we do not see ... Without data, it really is very, very complicated ...

You are correct. It really comes down to what the actual requirements are. 😉

_______________________________________________________________

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/

raym85 SSC Eights! Points: 938 More actions · Answer 11

here you go broski!

CREATE Table #TEMP

(

id varchar(100),

MID VARCHAR(100)

)

INSERT INTO #TEMP

VALUES ('100',NULL),

('200',NULL),

('300',NULL),

('400',NULL),

('500',NULL)

UPDATE OP

SET MID = OL.id

FROM (SELECT LL.ROW_ID, LL.ID,LL.MID

FROM(

SELECT ROW_ID =ROW_NUMBER() OVER (order by ID),ID,MID

FROM #TEMP I)LL)OP

JOIN (

SELECT ROW_ID =ROW_NUMBER() OVER (order by ID),ID,MID

FROM #TEMP I)OL ON OL.ROW_ID = OP.ROW_ID -1

SELECT * FROM #TEMP

DBA.k Ten Centuries Points: 1105 More actions · Answer 12

DBA.k

Ten Centuries

Points: 1105

March 21, 2013 at 9:18 pm

#1599658

thanks allot,

carlosaamaral Old Hand Points: 325 More actions · Answer 13

Jeff Moden (3/21/2013)
carlosaamaral (3/21/2013)
Sean Lange (3/21/2013)
carlosaamaral (3/21/2013)
Logic ?

MID=(CASE WHEN ID-100=0 THEN NULL
ELSE ID-100 END
)

That would work on the example data but I doubt the real situation is that straight forward. The code that Jeff and I posted makes it dynamic.
ok ... but sometimes the simple, we do not see ... Without data, it really is very, very complicated ...
Absolutely agreed. It's just that some of us have been doing this so long that we've learned to anticipate various data "possibilites" and to bulletproof the code a bit.

But this is not bad ... SQLSERVERCENTRAL Makes a great source of information, this is the most important .... Sometimes used truck engine in small cars, or kill the fly with large guns :w00t:, but this is a detail ... Congratulations to all!!