Query help to update with next records
-
Hi expert ,
Here is the details ,
DECLARE @t TABLE(col1 INT, col2 INT)
INSERT INTO @t(col1,col2)
SELECT 1, NULL UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, NULL UNION ALL
SELECT 4, NULL UNION ALL
SELECT 5, NULL UNION ALL
SELECT 6, 6 UNION ALL
SELECT 7, 7 UNION ALL
SELECT 8, 8 UNION ALL
SELECT 9, 9 UNION ALL
SELECT 10, NULL;
SELECT * FROM @T
-- Logic
Here i need to update if col2 has NULL then I need to col2 value to the NEXT col2 value .
from the given input data in row 3,4,5 col2 has NULL , So here we need to update row 3,4,5 to row6 of col2 .
similarly row 1 col2 value to row2 col2 vlaue ...
If last row of col2 has NULL then no need to update
-- EXPECTED OUTPUT:
col1col2
12
22
36
46
56
66
77
88
99
10NULL
Please help me .
Thanks!
-
Here it is (there can be another better solution as well)
with cte1as
( select col1,col2 from @t
),
cte2
as
(
select col1,col2 from @twhere col2 is not null
)
select col1,coalesce(col2,(select top 1 col2
from cte2 where cte2.col1>cte1.col1)) col22
from cte1;
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Without an ORDER BY, TOP is meaningless:
SELECTt1.col1,
col2 = ISNULL(t1.col2, x.col2)
FROM @T t1
OUTER APPLY (
SELECT TOP 1
t2.col2
FROM @T t2
WHERE t2.col1 > t1.col1
AND t2.col2 IS NOT NULL
AND t1.col2 IS NULL
ORDER BY t2.col1 ASC
) x
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
-
Thanks Chris for correcting me 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Here's an ( oldfashioned ) alternative so you can compare performance depending of the % of your set that needs to be updated.
Run it over a big set of data to figure out the version that serves your situation best.
Update U
set col2 = Ur.col2
from @t U
inner join (
Select T1.col1
, MIN(T2.col1) NextCol1
from @t T1
inner join @t T2
on T2.col1 > T1.col1
and T1.col2 is null
and T2.col2 is not null
group by T1.col1
) R
on R.col1 = U.col1
inner join @t Ur
on Ur.col1 = R.NextCol1
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks to all ,
-
ALZDBA (7/22/2012)
Here's an ( oldfashioned ) alternative so you can compare performance depending of the % of your set that needs to be updated.Run it over a big set of data to figure out the version that serves your situation best.
Excellent idea. Let's see who's created an accidental cross join or triangular join and who hasn't. 🙂 Here's code that will create 100,000 rows of data (whatever you want, really). I've setup the test data table so that the only thing anyone needs to change in their current for this problem is "@t" to "#T". As usual, details are in the code.
--===== Conditionally drop the test tables to make reruns easier in SSMSIF OBJECT_ID('tempdb..#T','U') IS NOT NULL DROP TABLE #T;
IF OBJECT_ID('tempdb..#N','U') IS NOT NULL DROP TABLE #N;
GO
--===== Declare and set a variable for the desired number of test rows.
-- I did it this way so that folks using 2K5 don't have to make
-- any changes to get it to work.
DECLARE @Rows INT;
SET @Rows = 100000;
--===== Create and populate the test table on-the-fly.
SELECT TOP (@Rows)
Col1 = ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT),0),
Col2 = CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT)
INTO #T
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Add a PK/Clustered Index to what looks like should be the PK.
ALTER TABLE #T
ADD PRIMARY KEY CLUSTERED (Col1)
;
--===== Try to nullify about 1/3rd of the columns.
-- About 28% will actually be nullified due to dupe updates.
-- The separate "#N" table gets rid of a major performance problem here.
SELECT TOP (@Rows/3)
Col1ToNullify = ABS(CHECKSUM(NEWID()))%@Rows+1
INTO #N
FROM #T
;
UPDATE tgt
SET Col2 = NULL
FROM #t tgt
INNER JOIN #N n
ON tgt.Col1 = n.Col1ToNullify
;
SELECT * FROM #T
;
--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) -
Nagaram (7/22/2012)
Thanks to all ,There's a question in my mind that's still outstanding about all of this. Do you just want to return a result set or do you want to update the original table?
--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) -
ChrisM@home (7/22/2012)
Without an ORDER BY, TOP is meaningless:SELECTt1.col1,
col2 = ISNULL(t1.col2, x.col2)
FROM @T t1
OUTER APPLY (
SELECT TOP 1
t2.col2
FROM @T t2
WHERE t2.col1 > t1.col1
AND t2.col2 IS NOT NULL
AND t1.col2 IS NULL
ORDER BY t2.col1 ASC
) x
This is pretty cool. With the right kind of indexing (nothing special, just the PK in this case), change it to an UPDATE, and add a WHERE clause to only work WHERE t1.Col2 IS NULL, it actually beats the Quirky Update that uses a safety counter. Not by much but clearly a win for two reasons. Obviously, it's faster and the other reason is because it's supported code.
For doing the same thing but in the same direction as the clustered index instead of the reverse, an "unfettered" QU still comes in 6 times faster processing a million rows in about 2 seconds on my old machine. Still, the method you wrote comes in at 12 seconds on a million rows with a 28% modification rate (28% of Col2 is NULL). Unlike the QU, you can control which rows get updated so you don't fire any triggers present on rows that don't need updating.
And, it's about as fast as some of the new "previous row" functionality available in 2012.
All of that is "geekinese" for "Well Done!" 🙂
--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) -
Jeff Moden (7/22/2012)
Nagaram (7/22/2012)
Thanks to all ,There's a question in my mind that's still outstanding about all of this. Do you just want to return a result set or do you want to update the original table?
I want to update the original table
-
Nagaram (7/22/2012)
Jeff Moden (7/22/2012)
Nagaram (7/22/2012)
Thanks to all ,There's a question in my mind that's still outstanding about all of this. Do you just want to return a result set or do you want to update the original table?
I want to update the original table
Then Chris' method works (very fast in the presence of a Clustered Index on Col1) with just a couple o' tweeks...
UPDATE t1SET col2 = x.col2
-- SELECT t1.col1, col2 = ISNULL(t1.col2, x.col2)
FROM @T t1
OUTER APPLY
(
SELECT TOP 1
t2.col2
FROM @T t2
WHERE t2.col1 > t1.col1
AND t2.col2 IS NOT NULL
AND t1.col2 IS NULL
ORDER BY t2.col1 ASC
) x
WHERE t1.Col2 IS NULL --Use this if updating
;
--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) -
Jeff Moden (7/22/2012)
ChrisM@home (7/22/2012)
Without an ORDER BY, TOP is meaningless:SELECTt1.col1,
col2 = ISNULL(t1.col2, x.col2)
FROM @T t1
OUTER APPLY (
SELECT TOP 1
t2.col2
FROM @T t2
WHERE t2.col1 > t1.col1
AND t2.col2 IS NOT NULL
AND t1.col2 IS NULL
ORDER BY t2.col1 ASC
) x
This is pretty cool. With the right kind of indexing (nothing special, just the PK in this case), change it to an UPDATE, and add a WHERE clause to only work WHERE t1.Col2 IS NULL, it actually beats the Quirky Update that uses a safety counter. Not by much but clearly a win for two reasons. Obviously, it's faster and the other reason is because it's supported code.
For doing the same thing but in the same direction as the clustered index instead of the reverse, an "unfettered" QU still comes in 6 times faster processing a million rows in about 2 seconds on my old machine. Still, the method you wrote comes in at 12 seconds on a million rows with a 28% modification rate (28% of Col2 is NULL). Unlike the QU, you can control which rows get updated so you don't fire any triggers present on rows that don't need updating.
And, it's about as fast as some of the new "previous row" functionality available in 2012.
All of that is "geekinese" for "Well Done!" 🙂
Jeff - I'm guessing this is the kind of answer that earns you the big bucks. 🙂
Now if I can just figure out what the heck you said. 😛
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Jeff Moden (7/22/2012)
Nagaram (7/22/2012)
Jeff Moden (7/22/2012)
Nagaram (7/22/2012)
Thanks to all ,There's a question in my mind that's still outstanding about all of this. Do you just want to return a result set or do you want to update the original table?
I want to update the original table
Then Chris' method works (very fast in the presence of a Clustered Index on Col1) with just a couple o' tweeks...
UPDATE t1SET col2 = x.col2
-- SELECT t1.col1, col2 = ISNULL(t1.col2, x.col2)
FROM @T t1
OUTER APPLY
(
SELECT TOP 1
t2.col2
FROM @T t2
WHERE t2.col1 > t1.col1
AND t2.col2 IS NOT NULL
AND t1.col2 IS NULL
ORDER BY t2.col1 ASC
) x
WHERE t1.Col2 IS NULL --Use this if updating
;
You know, it is really sad that people can't think for themselves. They're shown a method that can provide them with the answer the are looking for, but can't make the simple logic jump to do what they want. I mean, really, how hard is it to convert a select statement that displays the answer, to an update statement.
-
thanks for all for the valuable different methods . which were really help me to think in different perspectives.
I have gone thorough the all the solutions .
I will check all the above solutions with original data (one lac + records ..) for performance wise .
Thanks
~IRK
-
Lynn Pettis (7/22/2012)
Jeff Moden (7/22/2012)
Nagaram (7/22/2012)
Jeff Moden (7/22/2012)
Nagaram (7/22/2012)
Thanks to all ,There's a question in my mind that's still outstanding about all of this. Do you just want to return a result set or do you want to update the original table?
I want to update the original table
Then Chris' method works (very fast in the presence of a Clustered Index on Col1) with just a couple o' tweeks...
UPDATE t1SET col2 = x.col2
-- SELECT t1.col1, col2 = ISNULL(t1.col2, x.col2)
FROM @T t1
OUTER APPLY
(
SELECT TOP 1
t2.col2
FROM @T t2
WHERE t2.col1 > t1.col1
AND t2.col2 IS NOT NULL
AND t1.col2 IS NULL
ORDER BY t2.col1 ASC
) x
WHERE t1.Col2 IS NULL --Use this if updating
;
You know, it is really sad that people can't think for themselves. They're shown a method that can provide them with the answer the are looking for, but can't make the simple logic jump to do what they want. I mean, really, how hard is it to convert a select statement that displays the answer, to an update statement.
I'm not sure that the OP couldn't make or didn't try the leap. I had just gotten done testing it for performance and figured I'd post it. My question on whether the OP wanted a full result set or just to update the original table was so I'd know what to test for. The OP seemed quite happy with the answers previously given so it may be that he did actually make the leap and simply didn't post it.
--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)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply