Click here to monitor SSC
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
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 781
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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1548 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9733
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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1548 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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6966 Visits: 8838
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 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
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 781
Thanks to all ,
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44768 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44768 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44768 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Nagaram
Nagaram
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 781
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