January 30, 2018 at 7:04 pm
Hi,
In order to remove duplicates, I am using Flag column to specify whether row is active or not in Dimension Table
Ex
StagingTable
TempKey SLNNumber
356 45657
123 45657
Dimension Table
Key SLNNumber Row_is_Active
356 45657 Yes
123 'Unknown' No
I have to update Dimension table by using temp keys from StagingTable and Keys from Dimension table.I have to update only row where SLNNumber is Unknown.
I could write update statement like
update dimensiontable set Row_is_Active = 'No'
from StagingTable ST
where dimensiontable.key = ST.key and dimensiontable.SLNNumber = 'Unknown'
The above update statement will update when there is SLNnumber is unknown, but I am looking to update [Row_is_Active = 'No'] even if the SLN Number <> 'Unknown' when I receive only one row from StagingTable.
StagingTable
TempKey SLNNumber
222 99221
Dimension Table
Key SLNNumber Row_is_Active
222 99221 No (Need to update this column to 'No' though SLNnumber is not Unknown because I have received only one row from stagingtable).
Summary
StagingTable 1Row StraightUpdate in Dimension table
StagingTable 1+Rows, update only one value which has 'Unknown' SLN Number.
I am sure the above update sql statement wont satisfy my condition, could any help me how to handle this scenario
Many Thanks
January 31, 2018 at 2:56 am
No consumable DDL or sample data supplied, so I couldn't test, but this should work after any necessary tweaking.
WITH KeysandCounts AS (
SELECT
TempKey
, MAX(SLNNumber) AS SLNNumber
, COUNT(*) AS NoofRows
FROM StagingTable
GROUP BY TempKey
)
UPDATE d
SET Row_is_Active = 'No'
FROM DimensionTable d
JOIN KeysandCounts k
ON k.TempKey = d.Key
AND (k.NoofRows = 1 OR d.SLNNumber = 'Unknown')
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply