December 1, 2011 at 1:55 am
Hello,
I have a challenge. I want to update these rows without using a cursor.
This is my table:
AirlineID FlightID
854 85222
null 85421
null 8541
null 5214
452 9635
null 8521
null 8524
null 8526
null 8574
i would like to update the null Airline id with the last number before them. for example the airline id in the 2nd row will be 854 and is the 3rd and 4th. then any null after the 452 will be set to 452....and so on...
Any ideas?
Thanks.
December 1, 2011 at 2:03 am
Please will you provide full DDL in the form of CREATE TABLE statements, and sample data in the form of INSERT statements. A table is an unordered set, so which column will you use to choose the next or previous row?
John
December 1, 2011 at 2:07 am
I now added a primary key to this table. it's identity 1,2,3,4...
December 1, 2011 at 2:39 am
itay barda (12/1/2011)
I now added a primary key to this table. it's identity 1,2,3,4...
What John was asking you for was ddl scripts and readily consumable sample data, such as this: -
SELECT IDENTITY(INT,1,1) AS ID, AirlineID, FlightID
INTO #testEnvironment
FROM (SELECT 854, 85222
UNION ALL SELECT NULL, 85421
UNION ALL SELECT NULL, 8541
UNION ALL SELECT NULL, 5214
UNION ALL SELECT 452, 9635
UNION ALL SELECT NULL, 8521
UNION ALL SELECT NULL, 8524
UNION ALL SELECT NULL, 8526
UNION ALL SELECT NULL, 8574) a(AirlineID, FlightID)
That way, the unpaid volunteers on this site are able to quickly grasp your issue. Help us to help you!!
Here's one method to do what you're after, there are many more.
BEGIN TRAN
SELECT IDENTITY(INT,1,1) AS ID, AirlineID, FlightID
INTO #testEnvironment
FROM (SELECT 854, 85222
UNION ALL SELECT NULL, 85421
UNION ALL SELECT NULL, 8541
UNION ALL SELECT NULL, 5214
UNION ALL SELECT 452, 9635
UNION ALL SELECT NULL, 8521
UNION ALL SELECT NULL, 8524
UNION ALL SELECT NULL, 8526
UNION ALL SELECT NULL, 8574) a(AirlineID, FlightID)
UPDATE #testEnvironment
SET AirlineID = nAirlineID
FROM (SELECT b.ID AS nID, a.AirlineID AS nAirlineID, b.FlightID, a.ID AS aID, MAX(a.ID) OVER (PARTITION BY b.ID) AS amID
FROM #testEnvironment a
LEFT OUTER JOIN #testEnvironment b ON b.AirlineID IS NULL AND b.ID > a.ID
WHERE a.AirlineID IS NOT NULL) a
WHERE a.nID = ID AND a.aID = a.amID
SELECT * FROM #testEnvironment
ROLLBACK
December 1, 2011 at 2:49 am
Sorry about that guys. I appreciate your help.
Thank you very much for your time, and for the pro solution.
December 5, 2011 at 12:06 pm
Cadavre
I want to tell you how much I admire your solution to this post.
When I code, I pose questions to myself and answer them with my SQL. I pretty much always get to point B but I must admit that I don’t code at your level and am very interested in improving my technique.
Would you mind sharing your thought process as you developed this code? Maybe share what questions you ask yourself as you approach a problem like this?
December 5, 2011 at 12:13 pm
There's a concern with the premise of this problem. Cadavre's code illustrates the concern nicely. He had to include the identity() field.
You have no indicators in the original problem that states what row exists prior/after another. Celko would be proud, but it does cause other issues... like order is never guaranteed in SQL Server without an order by. There's nothing to order by on here.
If anyone else finds this code and intends to use it, make sure that identity exists in your table accurately before you start trying to use this code on the fly.
As to the thought process, I can't speak for Cadavre's mind, and I'm quite sure noone ever wants to try to figure out my own head. 🙂
However, the process is relatively simple, but you have to break it down into procedural steps.
1) Find all null records.
2) associate the previous record's data point (FlightID)
3) Update the NULL record to that data point.
From there, it's just coding.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 5, 2011 at 12:26 pm
Thank you for the quick response, Craig and I’m also a fan of your posts. I’m with you on the need for the identity column but some of the methodology used by Cadavre would never have occurred to me. Specifically the partitioning and the join (“LEFT OUTER JOIN #testEnvironment b on b.AirlineID is null and b.id > a.id”). I still tend to think of joins in terms of equivalence.
December 5, 2011 at 12:34 pm
jshahan (12/5/2011)
Thank you for the quick response, Craig and I’m also a fan of your posts. I’m with you on the need for the identity column but some of the methodology used by Cadavre would never have occurred to me. Specifically the partitioning and the join (“LEFT OUTTER JOIN #testEnvironment b on b.AirlineID is null and b.id > a.id”). I still tend to think of joins in terms of equivalence.
The key to this mechanism is this relatively lightweight looking piece of code:
MAX(a.ID) OVER (PARTITION BY b.ID)
Once you see that, and understand you're basically doing a max/group by, it will make a lot more sense.
The reason you don't see a simple > or < join is because they're known as triangle joins (there's a few articles out there on those keywords). Simply put, they're computational hogs if you can't partition the data effectively. They're rarely used and preferably only for one-off cases. They're a lot easier to write and understand but there's better ways (usually) to write something that's going to be under either long term or constant use.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply