February 11, 2014 at 10:54 am
Hi this is my DDL for this question.
Drop Table TemplateItemTest1
CREATE TABLE [dbo].[TemplateItemTest1](
[TID] [int] NOT NULL,
[LineID] [int] NOT NULL,
[ProductID] [int] NULL
)
Insert into TemplateItemTest1
(TID, LineID, ProductID)
SELECT 100, 1, 5 UNION ALL
SELECT 100, 2, 15 UNION ALL
SELECT 100, 3, 8 UNION ALL
SELECT 100, 4, 25 UNION ALL
SELECT 200, 1, 11 UNION ALL
SELECT 200, 2, 100 UNION ALL
SELECT 200, 3, 41 UNION ALL
SELECT 200, 4, 10 UNION ALL
SELECT 200, 5, 5 UNION ALL
SELECT 200, 6, 30
Select * from TemplateItemTest1
order by tid, lineid
this are my results if I select the current sample data,
SElect * from TemplateItemTest1
order by tid, lineid
10015
100215
10038
100425
200111
2002100
200341
200410
20055
200630
Now, if execute the following statement,
delete from TemplateItemTest1
where tid = 200
and lineid = 4
this is my new result
10015
100215
10038
100425
200111
2002100
200341
20055
200630
in this case I need to re-number the rows for TID 200 starting from 1 to 5.
I will appreciate any help on this.
Thanks in advance.
February 11, 2014 at 11:40 am
It is usually considered as a bad design to have resequencing records in a table for such case as your.
Any way, you can do this:
;with cte
as
(
select lineid
, row_number() over (partition by tid order by lineid) nl
from #TemplateItemTest1 where tid = 200
)
update t
set lineid = c.nl
from #TemplateItemTest1 t
join cte c on c.LineID = t.LineID
where t.tid = 200
Why do you want to resequence records? Just think about it: for deleting one record you are going to update N-rows , just in order to have some abstract number changed...
For sure you can come up with something better than that...
February 11, 2014 at 12:04 pm
I agree with your suggestions. I will re-consider my approach.
Thanks,
February 11, 2014 at 6:09 pm
You could consider doing the DELETE/UPDATE in one fell swoop using a MERGE.
WITH Template AS
(
SELECT TID, LineID, ProductID
,rn=ROW_NUMBER() OVER (PARTITION BY TID ORDER BY NULLIF(LineID, 4))
FROM TemplateItemTest1
WHERE TID = 200
)
MERGE TemplateItemTest1 t
USING Template s
ON t.tid = s.TID AND s.LineID = t.LineID
WHEN MATCHED AND t.LineID = 4 THEN
DELETE
WHEN MATCHED AND t.LineID <> 4 THEN
UPDATE SET LineID = rn-1;
This only works for deleting one LineID at a time, but I believe it could be modified to delete multiples with reordering of the rest.
Edit: Note that I also agree with Eugene's suggestion to reassess the design.
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
February 11, 2014 at 6:37 pm
Here it is deleting multiple LineIDs from multiple TIDs and then renumbering.
WITH RowsToDelete (TID, LineID) AS
(
SELECT 100, 3 UNION ALL SELECT 200, 2 UNION ALL SELECT 200, 4
),
CountItemsToDelete (TID, c) AS
(
SELECT TID, COUNT(*)
FROM RowsToDelete
GROUP BY TID
),
Template AS
(
SELECT a.TID, a.LineID, ProductID, LineID2=b.LineID, c
,rn=ROW_NUMBER() OVER (PARTITION BY a.TID ORDER BY NULLIF(a.LineID, b.LineID))
FROM TemplateItemTest1 a
LEFT JOIN RowsToDelete b ON a.TID = b.TID AND a.LineID = b.LineID
JOIN CountItemsToDelete c ON a.TID = c.TID
)
MERGE TemplateItemTest1 t
USING Template s
ON t.tid = s.TID AND s.LineID = t.LineID
WHEN MATCHED AND s.LineID2 IS NOT NULL THEN
DELETE
WHEN MATCHED AND s.LineID2 IS NULL THEN
UPDATE SET LineID = rn-c;
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
February 12, 2014 at 9:14 am
Thank you!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply