Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

i want update the 2nd column based on first column: Expand / Collapse
Author
Message
Posted Thursday, March 21, 2013 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 12,928, Visits: 12,345
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1433970
Posted Thursday, March 21, 2013 12:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 6, 2014 12:39 PM
Points: 376, Visits: 172
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
Posted Thursday, March 21, 2013 9:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, September 1, 2013 8:24 PM
Points: 17, Visits: 127
thanks allot,
Post #1434120
Posted Saturday, March 23, 2013 5:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 29, Visits: 992
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 , but this is a detail ... Congratulations to all!!
Post #1434590
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse