January 23, 2009 at 4:36 pm
My table data is
OrderNo SeqNo Type
1234 1 BL
1234 2 "
1234 3 GL
1235 1 PL
1235 2 "
For a order number, I want to replace Type = " as previous
Type value of previous SeqNo.
So, updated data should be
OrderNo SeqNo Type
1234 1 BL
1234 2 BL
1234 3 GL
1235 1 PL
1235 2 PL
If anyone can highlight on this, I'll be very appreciated.
Thanks in advance!!!
January 23, 2009 at 5:28 pm
Your not giving us enough information.
How do we know which row is the first row?
The one with the value or the one that is blank.
Do you have an identity column, or Date field to use to order.
you have to remember data in a table has no order Unless there is a clustered index on a specific field.
So First has no context with the data you have posted.
January 23, 2009 at 7:36 pm
Actually... good post... I get it. To make future posts just absolutely perfect, take a look at the link in my signature below... THAT's how we like to see data posted and we'll normally respond to such correctly formatted posts very quickly.
Here's your answer... and take a look at how I created the table and the data...that's what we'd like to see in future posts.
Lemme know if it works for you...
drop table #test
go
--===== Create a test table and populate it.
-- This is the way we like to see data in posts to make it easier for us
-- so you can get a better answer quicker.
-- This is NOT a part of the solution.
CREATE TABLE #Test
(OrderNo INT, SeqNo INT, Type CHAR(2))
INSERT INTO #Test
(OrderNo, SeqNo, Type)
SELECT 1234,1,'BL' UNION ALL
SELECT 1234,2,'' UNION ALL
SELECT 1234,3,'' UNION ALL
SELECT 1234,4,'GL' UNION ALL
SELECT 1235,1,'PL' UNION ALL
SELECT 1235,2,''
--===== Display the original content of the table
SELECT OrderNo, SeqNo, Type
FROM #Test t1
ORDER BY OrderNo, SeqNo
--===== Do a "data smear" using an ordered "Quirky" update.
DECLARE @PrevOrderNo INT,
@PrevType CHAR(2)
UPDATE t1
SET @PrevType = Type = CASE WHEN Type > '' THEN t1.Type ELSE @PrevType END,
@PrevOrderNo = t1.OrderNo
FROM #Test t1
INNER JOIN
(--==== This forces the order of the update
SELECT TOP 100 PERCENT
OrderNo, SeqNo
FROM #Test
ORDER BY OrderNo, SeqNo
)t2
ON t1.OrderNo = t2.OrderNo
AND t1.SeqNo = t2.SeqNo
--===== Display the new content of the table
SELECT OrderNo, SeqNo, Type
FROM #Test t1
ORDER BY OrderNo, SeqNo
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2009 at 7:48 pm
Ray M (1/23/2009)
Your not giving us enough information.How do we know which row is the first row?
The one with the value or the one that is blank.
Do you have an identity column, or Date field to use to order.
you have to remember data in a table has no order Unless there is a clustered index on a specific field.
So First has no context with the data you have posted.
Ray, the "order" is by OrderNo and SeqNo and a clustered index doesn't guarantee the order on SELECTs... it does on the "quirky" UPDATE, but only if it's in the order you want and only if you force an index scan on the UPDATE using a WITH(INDEX(0)) hint.
If you don't mind code running a bit slower, you can force an ORDER BY on the UPDATE without a clustered index like I did in the code above.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 23, 2009 at 10:34 pm
Jeff tons of thanks for this solution. It works perfect for my data. I would definately like to know logic behind this if you can throw some light on this I will be highly appreciated.
January 23, 2009 at 11:53 pm
January 24, 2009 at 6:54 am
hemin.shah85 (1/23/2009)
Jeff tons of thanks for this solution. It works perfect for my data. I would definately like to know logic behind this if you can throw some light on this I will be highly appreciated.
Seth is correct... I wrote an article on it and it's the one he pointed out.
There is an over-statement in the article... you shouldn't use an index hint to sort a SELECT, always use an ORDER BY for those ... The method of using an index hint does work on the UPDATEs though.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2009 at 7:02 am
Garadin (1/23/2009)
He wrote an article on it actually. See the Running Totals link in my signature.
Heh... I'm getting closer... normally, you beat me to this on providing solutions. I finally got one in but you still beat me at the explanation. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2009 at 7:20 am
Hemin,
There's another way to do the same thing that's actually much faster if you have a large table you need to do this on. It, too, is covered in the article that Seth pointed you to... I've changed the code above to use that faster method and I've updated the comments in the code to point out the changes... do read the comments... 😉 and do lookup the table hint of WITH(INDEX(0))... THAT's what makes it work on these types of UPDATES...
drop table #test
go
--===== Create a test table and populate it.
-- This is the way we like to see data in posts to make it easier for us
-- so you can get a better answer quicker.
-- This is now a part of the solution. Notice the NOT NULL's on the columns
-- that we'll use in the clustered index (PK in this case)
CREATE TABLE #Test
(OrderNo INT NOT NULL, SeqNo INT NOT NULL, Type CHAR(2))
INSERT INTO #Test
(OrderNo, SeqNo, Type)
SELECT 1234,1,'BL' UNION ALL
SELECT 1234,2,'' UNION ALL
SELECT 1234,3,'' UNION ALL
SELECT 1234,4,'GL' UNION ALL
SELECT 1235,1,'PL' UNION ALL
SELECT 1235,2,''
--===== This is part of the change... it's a Clustered Index (PK in this case)
-- that the quirky update will use to SCAN in the correct order
-- without an ORDER BY. Again, DO NOT USE for SELECTs. It only
-- works for the "quirky update" method. There, it works whether
-- the index/constraint is named, or not. If it's a PK constraint,
-- it shouldn't be named on TempTables because constraint names
-- must be unique whereas index names do not need to be.
ALTER TABLE #Test
ADD PRIMARY KEY CLUSTERED (OrderNo, SeqNo)
--===== Display the original content of the table
SELECT OrderNo, SeqNo, Type
FROM #Test t1
ORDER BY OrderNo, SeqNo
--===== Do a "data smear" using an ordered "Quirky" update.
DECLARE @PrevOrderNo INT,
@PrevType CHAR(2)
-- This is the other part of the change. The quirky update will always do
-- things in the correct order (assumming the clustered index is on the
-- correct columns) IF you can force a clustered index scan. The WITH(INDEX(0))
-- directive (table hint) forces that scan. As you can see, it'll do two things...
-- it makes the code a heck of a lot shorter and it makes it lightning fast.
-- This method will update a million rows in less than 7 seconds.
-- Basically, it reads a row, set's all the variables as if you were using VB or C,
-- and writes the change all in one statement. NASTY FAST!
UPDATE t1
SET @PrevType = Type = CASE WHEN Type > '' THEN t1.Type ELSE @PrevType END,
@PrevOrderNo = t1.OrderNo
FROM #Test t1 WITH(INDEX(0))
--===== Display the new content of the table
SELECT OrderNo, SeqNo, Type
FROM #Test t1
ORDER BY OrderNo, SeqNo
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2009 at 9:43 am
Jeff Moden (1/24/2009)
Garadin (1/23/2009)
He wrote an article on it actually. See the Running Totals link in my signature.Heh... I'm getting closer... normally, you beat me to this on providing solutions. I finally got one in but you still beat me at the explanation. 🙂
Way to hold out on me with the whole Order by trick by the way!
January 24, 2009 at 10:21 am
Jeff you are right. With(index(0)) works really fast...as I have a dataset of 6 million records..Thanks for your help..But I was thinking, can we do this without updating that is just with SELECT statement so as to display the values only..
January 24, 2009 at 11:05 am
hemin.shah85 (1/24/2009)
Jeff you are right. With(index(0)) works really fast...as I have a dataset of 6 million records..Thanks for your help..But I was thinking, can we do this without updating that is just with SELECT statement so as to display the values only..
Unfortunately, no, not with this approach.
You see, SELECT has a tiny restriction with respect to variable assignments: a SELECT statement cannot assing to both variables and to column outputs. Since variables are scalar and singular they cannot hold more than one row "cell"'s worth of data. On the other hand, output column values are distinct by row and cannot be referenced outside of their row. The UPDATE trick works by using both together: output columns to output each distinct row's values, and variables to carry values across rows: from one row to the next.
So this one tiny restriction prevents the whole trick from working with SELECT (unless you only want a single row's worth of output).
...
Now having said that, let me also say that several months ago, I figured out a way to do the UPDATE trick with a couple of SELECT's. Unfortunately it is both complex and slow. In fact, it is typically slower than several other ways of doing the same thing, including cursors and loops most of the time. (I have been toying around with a couple of possibilities for making it faster, but it makes this complex technique even more so).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 24, 2009 at 12:10 pm
hemin.shah85 (1/24/2009)
Jeff you are right. With(index(0)) works really fast...as I have a dataset of 6 million records..Thanks for your help..But I was thinking, can we do this without updating that is just with SELECT statement so as to display the values only..
I'm thinking that you didn't actually read the article you were pointed to and suggest that you actually take the time to do that. 😉 There, you'll find suggestions on what to do when you can't actually update the source table. Some of those options include the use of SELECT/INTO (a very high speed method) of the data you need to do the mod on into a TempTable, do the update on THAT, and then display from there. It'll still be light years ahead of any other method you can use for this.
Like I said, read the article...
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2009 at 12:12 pm
Garadin (1/24/2009)
Way to hold out on me with the whole Order by trick by the way!
BWAA-HAA!! 😛 Dude! 😀 No hold out... it's in the article, too! :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2009 at 12:34 pm
Jeff Moden (1/24/2009)
hemin.shah85 (1/24/2009)
Jeff you are right. With(index(0)) works really fast...as I have a dataset of 6 million records..Thanks for your help..But I was thinking, can we do this without updating that is just with SELECT statement so as to display the values only..I'm thinking that you didn't actually read the article you were pointed to and suggest that you actually take the time to do that. 😉 There, you'll find suggestions on what to do when you can't actually update the source table. Some of those options include the use of SELECT/INTO (a very high speed method) of the data you need to do the mod on into a TempTable, do the update on THAT, and then display from there. It'll still be light years ahead of any other method you can use for this.
Just to clarify my earlier response to Hemin's question: I was assuming that he was asking about using SELECT pseudocursors to do this. There are of course lots of ways to accomplish the same result, many of which use SELECT at one point or another.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply