March 15, 2018 at 2:29 am
I'm doing validation, comparing if two tables have the save rows and result using except and intersect function.
If the except function produce results I was to update only the records that doesn't match and if intersect I want to update the records that matched.
My query only excecute the first update query and throw error on the second update 'Invalid object name MatchedResults'
;WITH Migration
AS (SELECT ac.Id
,ac.Code AS ComponentCode
,ac2.Code AS ParentComponentCode
,aat.Code AS AssetTypeCode
,CASE WHEN ac.SiteId = 1000 THEN 'SVP'
WHEN ac.SiteId = 1001 THEN 'OSD'
WHEN ac.SiteId = 1002 THEN 'ELN'
END AS SiteCode
FROM [PRODS-SUPPORT\SQL2014].DEMO_ASPEN_CIS.dbo.astComponents
AS ac
INNER JOIN [PRODS-SUPPORT\SQL2014].DEMO_ASPEN_CIS.dbo.astAssetTypes
AS aat ON ac.AssetTypeId = aat.Id
LEFT JOIN [PRODS-SUPPORT\SQL2014].DEMO_ASPEN_CIS.dbo.astComponents
AS ac2 ON ac.ParentComponentId = ac2.Id
),
BRR
AS (SELECT aatc.OK5_MigrationId AS Id
,aatc.Code AS ComponentCode
,aatc.ParentComponentCode
,aatc.AssetTypeCode
,aatc.SiteId AS SiteCode
FROM dbo.astAssetTypesComponents AS aatc
),
UnMatched
AS (SELECT BRR.Id
,BRR.ComponentCode
,BRR.ParentComponentCode
,BRR.AssetTypeCode
,BRR.SiteCode
FROM BRR
EXCEPT
SELECT Migration.Id
,Migration.ComponentCode COLLATE Latin1_General_CI_AS AS ComponentCode
,Migration.ParentComponentCode COLLATE Latin1_General_CI_AS AS ParentComponentCode
,Migration.AssetTypeCode COLLATE Latin1_General_CI_AS AS AssetTypeCode
,Migration.SiteCode COLLATE Latin1_General_CI_AS AS SiteCode
FROM Migration
),
MatchedResults
AS (SELECT BRR.Id
,BRR.ComponentCode
,BRR.ParentComponentCode
,BRR.AssetTypeCode
,BRR.SiteCode
FROM BRR
INTERSECT
SELECT Migration.Id
,Migration.ComponentCode COLLATE Latin1_General_CI_AS AS ComponentCode
,Migration.ParentComponentCode COLLATE Latin1_General_CI_AS AS ParentComponentCode
,Migration.AssetTypeCode COLLATE Latin1_General_CI_AS AS AssetTypeCode
,Migration.SiteCode COLLATE Latin1_General_CI_AS AS SiteCode
FROM Migration
)
UPDATE dbo.astAssetTypesComponents
SET Validation_Commentary = 'unMatched'
FROM UnMatched
WHERE astAssetTypesComponents.OK5_MigrationId = UnMatched.Id
UPDATE dbo.astAssetTypesComponents
SET Validation_Commentary = 'Validation Passed'
FROM MatchedResults
WHERE astAssetTypesComponents.OK5_MigrationId = MatchedResults.Id;
March 15, 2018 at 2:53 am
Two issues here. Firstly, you can only "use" the CTE's once and you're attempting to "use" them twice:
UPDATE dbo.astAssetTypesComponents
SET Validation_Commentary = 'unMatched'
FROM UnMatched
WHERE astAssetTypesComponents.OK5_MigrationId = UnMatched.Id
UPDATE dbo.astAssetTypesComponents
SET Validation_Commentary = 'Validation Passed'
FROM MatchedResults
WHERE astAssetTypesComponents.OK5_MigrationId = MatchedResults.Id;
Secondly, your update statements are misconfigured:
UPDATE c
SET Validation_Commentary = 'unMatched'
FROM dbo.astAssetTypesComponents c
INNER JOIN UnMatched u
ON c.OK5_MigrationId = u.Id
UPDATE c
SET Validation_Commentary = 'Validation Passed'
FROM dbo.astAssetTypesComponents c
INNER JOIN MatchedResults m
ON c.OK5_MigrationId = m.Id;
For fast, accurate and documented assistance in answering your questions, please read this article.
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
March 15, 2018 at 3:05 am
Personally, I'd write it like this - I think it's less prone to interpretation errors and will only read the remote tables once (and if you're smart, and lucky, you could get the lot into one query - so long as it's more efficient than the original):
SELECT
ac.Id,
ac.Code COLLATE Latin1_General_CI_AS AS ComponentCode,
ac2.Code COLLATE Latin1_General_CI_AS AS ParentComponentCode,
aat.Code COLLATE Latin1_General_CI_AS AS AssetTypeCode,
CASE
WHEN ac.SiteId = 1000 THEN 'SVP'
WHEN ac.SiteId = 1001 THEN 'OSD'
WHEN ac.SiteId = 1002 THEN 'ELN'
END COLLATE Latin1_General_CI_AS AS SiteCode
INTO #Migration
FROM [PRODS-SUPPORT\SQL2014].DEMO_ASPEN_CIS.dbo.astComponents AS ac
INNER JOIN [PRODS-SUPPORT\SQL2014].DEMO_ASPEN_CIS.dbo.astAssetTypes AS aat
ON ac.AssetTypeId = aat.Id
LEFT JOIN [PRODS-SUPPORT\SQL2014].DEMO_ASPEN_CIS.dbo.astComponents AS ac2
ON ac.ParentComponentId = ac2.Id
UPDATE c
SET Validation_Commentary = 'unMatched'
FROM dbo.astAssetTypesComponents c
WHERE NOT EXISTS (
SELECT 1
FROM #Migration m
WHERE m.Id = c.OK5_MigrationId
AND m.ComponentCode = c.Code
AND m.ParentComponentCode = c.ParentComponentCode
AND m.AssetTypeCode = c.AssetTypeCode
AND m.SiteCode = c.SiteId
)
UPDATE c
SET Validation_Commentary = 'Validation Passed'
FROM dbo.astAssetTypesComponents c
WHERE EXISTS (
SELECT 1
FROM #Migration m
WHERE m.Id = c.OK5_MigrationId
AND m.ComponentCode = c.Code
AND m.ParentComponentCode = c.ParentComponentCode
AND m.AssetTypeCode = c.AssetTypeCode
AND m.SiteCode = c.SiteId
)
For fast, accurate and documented assistance in answering your questions, please read this article.
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
March 15, 2018 at 3:18 am
thanks for the response
March 15, 2018 at 3:48 am
Hi Chris
your query work perfectly. it's what I wanted.
Thank you kindly
March 15, 2018 at 3:50 am
You're welcome, thank you for the feedback.
For fast, accurate and documented assistance in answering your questions, please read this article.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply