How do I update a second table from a cte so that it works reliably
up vote
0
down vote
favorite
It would be great to get some advice on the following Robot performance data problem that is driving me crazy! We have 23 Robots in 5 Facilities and Spot data (placement and pickup locations) from all of them is collected into one big table. I have two tables #Temp2 and #Temp2d as follows:
#Temp2
ID Spot LastDiff
76544 21 23
76545 21 0
76546 23 21
76547 23 0
76548 21 35
76549 23 21
76550 21 23
76551 23 21
#Temp2d
IDmin Spot LastDiff
76544 21 23
76546 23 21
76548 21 23
76549 23 21
76550 21 23
76551 23 21
The table #Temp2d is derived from #Temp2 and records the individual changes in Spot number (repeat Spot numbers have been removed). (The 90,000 record Spot number data is partitioned by two other columns not shown, FacilityName and Robot).
I'm then adding a new column "LastDiff" to #Temp2 to hold the last different value of Spot before it changed. To update the LastDiff column of #Temp2 the following update query is used:
;with cte_1 as
(
select t1.IDmin, t1.spot, t1.lastDiff
from
(
select
IDmin
, spot
, LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot) AS lastDiff
from #Temp2d
) t1
)
update #Temp2 set #Temp2.lastDiff = cte_1.lastDiff
from cte_1
where #Temp2.ID = cte_1.IDmin
The problem is that in the 90,000 row dataset around 30 values of LastDiff in #Temp2 are wrong, as shown in row 76,548 in the above #Temp2 table. There does not seem to be any pattern to the errors, they occur randomly throughout the dataset. Any assistance is resolving this problem would be hugely appreciated.
tsql sql-update common-table-expression partitioning
asked Nov 9 at 8:22
Fyll See
297
add a comment |
up vote
0
down vote
favorite
It would be great to get some advice on the following Robot performance data problem that is driving me crazy! We have 23 Robots in 5 Facilities and Spot data (placement and pickup locations) from all of them is collected into one big table. I have two tables #Temp2 and #Temp2d as follows:
#Temp2
ID Spot LastDiff
76544 21 23
76545 21 0
76546 23 21
76547 23 0
76548 21 35
76549 23 21
76550 21 23
76551 23 21
#Temp2d
IDmin Spot LastDiff
76544 21 23
76546 23 21
76548 21 23
76549 23 21
76550 21 23
76551 23 21
The table #Temp2d is derived from #Temp2 and records the individual changes in Spot number (repeat Spot numbers have been removed). (The 90,000 record Spot number data is partitioned by two other columns not shown, FacilityName and Robot).
I'm then adding a new column "LastDiff" to #Temp2 to hold the last different value of Spot before it changed. To update the LastDiff column of #Temp2 the following update query is used:
;with cte_1 as
(
select t1.IDmin, t1.spot, t1.lastDiff
from
(
select
IDmin
, spot
, LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot) AS lastDiff
from #Temp2d
) t1
)
update #Temp2 set #Temp2.lastDiff = cte_1.lastDiff
from cte_1
where #Temp2.ID = cte_1.IDmin
The problem is that in the 90,000 row dataset around 30 values of LastDiff in #Temp2 are wrong, as shown in row 76,548 in the above #Temp2 table. There does not seem to be any pattern to the errors, they occur randomly throughout the dataset. Any assistance is resolving this problem would be hugely appreciated.
tsql sql-update common-table-expression partitioning
asked Nov 9 at 8:22
Fyll See
297
2
Might be, that theORDER BYin yourLAG()'sOVER-clause is not correct. You must use a column to order which ensures the sorting you need...
– Shnugo
Nov 9 at 8:30
ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
– Fyll See
Nov 9 at 8:55
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
It would be great to get some advice on the following Robot performance data problem that is driving me crazy! We have 23 Robots in 5 Facilities and Spot data (placement and pickup locations) from all of them is collected into one big table. I have two tables #Temp2 and #Temp2d as follows:
#Temp2
ID Spot LastDiff
76544 21 23
76545 21 0
76546 23 21
76547 23 0
76548 21 35
76549 23 21
76550 21 23
76551 23 21
#Temp2d
IDmin Spot LastDiff
76544 21 23
76546 23 21
76548 21 23
76549 23 21
76550 21 23
76551 23 21
The table #Temp2d is derived from #Temp2 and records the individual changes in Spot number (repeat Spot numbers have been removed). (The 90,000 record Spot number data is partitioned by two other columns not shown, FacilityName and Robot).
I'm then adding a new column "LastDiff" to #Temp2 to hold the last different value of Spot before it changed. To update the LastDiff column of #Temp2 the following update query is used:
;with cte_1 as
(
select t1.IDmin, t1.spot, t1.lastDiff
from
(
select
IDmin
, spot
, LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot) AS lastDiff
from #Temp2d
) t1
)
update #Temp2 set #Temp2.lastDiff = cte_1.lastDiff
from cte_1
where #Temp2.ID = cte_1.IDmin
The problem is that in the 90,000 row dataset around 30 values of LastDiff in #Temp2 are wrong, as shown in row 76,548 in the above #Temp2 table. There does not seem to be any pattern to the errors, they occur randomly throughout the dataset. Any assistance is resolving this problem would be hugely appreciated.
tsql sql-update common-table-expression partitioning
asked Nov 9 at 8:22
Fyll See
297
It would be great to get some advice on the following Robot performance data problem that is driving me crazy! We have 23 Robots in 5 Facilities and Spot data (placement and pickup locations) from all of them is collected into one big table. I have two tables #Temp2 and #Temp2d as follows:
#Temp2
ID Spot LastDiff
76544 21 23
76545 21 0
76546 23 21
76547 23 0
76548 21 35
76549 23 21
76550 21 23
76551 23 21
#Temp2d
IDmin Spot LastDiff
76544 21 23
76546 23 21
76548 21 23
76549 23 21
76550 21 23
76551 23 21
The table #Temp2d is derived from #Temp2 and records the individual changes in Spot number (repeat Spot numbers have been removed). (The 90,000 record Spot number data is partitioned by two other columns not shown, FacilityName and Robot).
I'm then adding a new column "LastDiff" to #Temp2 to hold the last different value of Spot before it changed. To update the LastDiff column of #Temp2 the following update query is used:
;with cte_1 as
(
select t1.IDmin, t1.spot, t1.lastDiff
from
(
select
IDmin
, spot
, LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot) AS lastDiff
from #Temp2d
) t1
)
update #Temp2 set #Temp2.lastDiff = cte_1.lastDiff
from cte_1
where #Temp2.ID = cte_1.IDmin
The problem is that in the 90,000 row dataset around 30 values of LastDiff in #Temp2 are wrong, as shown in row 76,548 in the above #Temp2 table. There does not seem to be any pattern to the errors, they occur randomly throughout the dataset. Any assistance is resolving this problem would be hugely appreciated.
tsql sql-update common-table-expression partitioning
tsql sql-update common-table-expression partitioning
asked Nov 9 at 8:22
Fyll See
297
asked Nov 9 at 8:22
Fyll See
297
edited Nov 9 at 8:36
asked Nov 9 at 8:22
Fyll See
297
asked Nov 9 at 8:22
Fyll See
297
asked Nov 9 at 8:22
Fyll See
297
297
2
Might be, that theORDER BYin yourLAG()'sOVER-clause is not correct. You must use a column to order which ensures the sorting you need...
– Shnugo
Nov 9 at 8:30
ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
– Fyll See
Nov 9 at 8:55
add a comment |
2
Might be, that theORDER BYin yourLAG()'sOVER-clause is not correct. You must use a column to order which ensures the sorting you need...
– Shnugo
Nov 9 at 8:30
ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
– Fyll See
Nov 9 at 8:55
2
2
Might be, that the
ORDER BY in your LAG()'s OVER-clause is not correct. You must use a column to order which ensures the sorting you need...– Shnugo
Nov 9 at 8:30
Might be, that the
ORDER BY in your LAG()'s OVER-clause is not correct. You must use a column to order which ensures the sorting you need...– Shnugo
Nov 9 at 8:30
ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
– Fyll See
Nov 9 at 8:55
ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
– Fyll See
Nov 9 at 8:55
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53222104%2fhow-do-i-update-a-second-table-from-a-cte-so-that-it-works-reliably%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Might be, that the
ORDER BYin yourLAG()'sOVER-clause is not correct. You must use a column to order which ensures the sorting you need...– Shnugo
Nov 9 at 8:30
ha ha thanks so much - talk about overlooking the obvious! adding IDmin to the partition order-by did the trick: LAG(spot,1,0) OVER(PARTITION BY FacilityName, Robot ORDER BY FacilityName, Robot, IDmin) AS lastDiff
– Fyll See
Nov 9 at 8:55