SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query help to update with next records


Query help to update with next records

Author
Message
Nagaram
Nagaram
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 803
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!
Lokesh Vij
Lokesh Vij
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3304 Visits: 1599
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


ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5176 Visits: 10605
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
Lokesh Vij
Lokesh Vij
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3304 Visits: 1599
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


ALZDBA
ALZDBA
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29665 Visits: 8986
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Nagaram
Nagaram
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 803
Thanks to all ,
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214024 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214024 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214024 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Nagaram
Nagaram
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 803
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search