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 123»»»

Query help to update with next records Expand / Collapse
Author
Message
Posted Saturday, July 21, 2012 11:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, February 23, 2014 12:27 AM
Points: 94, Visits: 717
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 :
col1 col2
1 2
2 2
3 6
4 6
5 6
6 6
7 7
8 8
9 9
10 NULL

Please help me .

Thanks!
Post #1333445
Posted Sunday, July 22, 2012 1:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Here it is (there can be another better solution as well)

with cte1
as
( 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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1333453
Posted Sunday, July 22, 2012 3:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:06 AM
Points: 1,076, Visits: 6,452
Without an ORDER BY, TOP is meaningless:

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




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1333467
Posted Sunday, July 22, 2012 3:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Thanks Chris for correcting me

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1333468
Posted Sunday, July 22, 2012 5:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:38 AM
Points: 7,004, Visits: 8,448
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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
Post #1333471
Posted Sunday, July 22, 2012 8:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, February 23, 2014 12:27 AM
Points: 94, Visits: 717
Thanks to all ,
Post #1333489
Posted Sunday, July 22, 2012 6:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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 SSMS
IF 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1333557
Posted Sunday, July 22, 2012 6:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1333559
Posted Sunday, July 22, 2012 7:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
ChrisM@home (7/22/2012)
Without an ORDER BY, TOP is meaningless:

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



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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1333562
Posted Sunday, July 22, 2012 7:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, February 23, 2014 12:27 AM
Points: 94, Visits: 717
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
Post #1333565
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse