October 9, 2013 at 5:18 pm
I am in the process of converting some old tables into a new format and I need to do an update as an intermediate step. I thought this would be fairly straight forward, but of course. . . 🙁
Here is the DDL and Insert statements:
declare @t1 table
(
[RIN] [int],
[Type] [tinyint],
[Code] [int],
[Lot] [varchar](50),
[ShouldBe] [int] NULL
)
insert @t1 values
(130363,2,145,0033,NULL)
,(132757,2,145,0033,NULL)
,(165041,2,145,0033,NULL)
,(160574,2,145,0034,NULL)
,(160575,2,145,0034,NULL)
,(132763,2,145,0035,NULL)
,(137219,2,145,0035,NULL)
,(140963,2,145,0035,NULL)
What I am trying to do is point the duplicate rows ShouldBe column to point to the RIN of the first row of the duplicates.
I have tried the following, but I am pretty sure that I am violating an update principle of SQL.
;with cte as
(
select RIN, TYPE, Code, Lot, ShouldBe,
ROW_NUMBER() over (Partition by Type, Code, Lot order by RIN) RowNum
from @t1
)
update c set
c.ShouldBe =
case when c.RowNum = 1 then 0
when c.RowNum = 2 then p.RIN
else p.ShouldBe
end
from cte as c
left outer join cte as p
on c.RowNum = p.rownum + 1
and c.Lot = p.Lot
select *, ROW_NUMBER() over (Partition by Type, Code, Lot order by RIN) RowNum
from @t1
This is the output I am looking for:
RINTypeCodeLotShouldBe RowNum
13036321453301
1327572145331303632
1650412145331303633
16057421453401
1605752145341605742
13276321453501
1372192145351327632
1409632145351327633
Any thoughts?
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 9, 2013 at 5:35 pm
update t1
set ShouldBe=minrin
from @t1 t1
join (
select type,code,lot, min(RIN) minrin
from @t1
group by type,code,lot
) g
on g.type = t1.Type
and g.lot = t1.Lot
and g.code = t1.code
where t1.rin<>g.minrin
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);October 9, 2013 at 6:57 pm
Or another option
with cte as
(
select RIN, TYPE, Code, Lot, ShouldBe,
MIN(RIN) over (Partition by Type, Code, Lot) val
from @t1
)
update c set
shouldbe = val
from cte as c
where rin <> val;
Of course this produces exactly the same plan as mr magoo's 🙂
October 9, 2013 at 7:21 pm
Not as compact but just for the record, here's another way:
WITH ShouldBe AS
(
SELECT a.[RIN], [Type], [Code], [Lot], ShouldBe
,RIN2=b.[RIN]
FROM @t1 a
CROSS APPLY
(
SELECT TOP 1 [RIN]
FROM @t1 b
WHERE a.[Type] = b.[Type] AND a.[Code] = b.[Code] AND a.[Lot] = b.[Lot]
ORDER BY [RIN]
) b
)
UPDATE a
SET ShouldBe = CASE [RIN] WHEN RIN2 THEN 0 ElSE RIN2 END
FROM ShouldBe a;
SELECT *
FROM @t1;
Note that I believe with an appropriate PRIMARY KEY ([Type], [Code], [Lot], [RIN]) this could also probably be done with a Quirky Update[/url] (if speed is important and you don't have any personal issues using that approach).
SQL 2012 also has some nifty new features like the window frame that could be applied here.
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
October 9, 2013 at 11:23 pm
All 3 approaches worked! Awesome forum.
I really liked the Grouping because is was so straightforward. Why didn't I think of grouping! (Duh!)
Of course nothing beats a cte except a cte with a Cross Apply in it!
You have given me something I can study and learn from. I need learn to be able to "see" a Cross Apply as easily as I see regular joins.
Thank you.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 9, 2013 at 11:32 pm
LinksUp (10/9/2013)
All 3 approaches worked! Awesome forum.I really liked the Grouping because is was so straightforward. Why didn't I think of grouping! (Duh!)
Of course nothing beats a cte except a cte with a Cross Apply in it!
You have given me something I can study and learn from. I need learn to be able to "see" a Cross Apply as easily as I see regular joins.
Thank you.
Mr. Magoo's GROUP BY could also be used in my CROSS APPLY... then you'd have it all!
You're welcome.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply