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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » i want update the 2nd column based on first...

14 posts, Page 2 of 2

««1 2

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

Rate Topic

Display Mode

Topic Options

Author

Message

Sean Lange

Posted Thursday, March 21, 2013 12:41 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:52 PM
Points: 8,605, Visits: 8,246

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. Wink

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1433970

raym85

Posted Thursday, March 21, 2013 12:43 PM

Old Hand

Group: General Forum Members
Last Login: Friday, May 17, 2013 3:51 PM
Points: 376, Visits: 165

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

Post #1433972

mahi123

Posted Thursday, March 21, 2013 9:18 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 1:46 AM
Points: 14, Visits: 96

thanks allot,

Post #1434120

carlosaamaral

Posted Saturday, March 23, 2013 5:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Today @ 5:05 AM
Points: 28, Visits: 863

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!!

Post #1434590

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions