Hi Folks.
Can someone with a bit more knowledge and experience here help validate something for me? I'm working with two tables, a source and a target, the target table is regularly updated with new values from the source. However, there are instances where a row or more will be deleted from the source table but that is not reflected in the updates to the target table. The target table provides reports that are currently being validated against the source table and the reports are inaccurate due to the deleted rows not being accounted for in the target table.
My solution is to compare the value of a field called RcID in the target table, to all RcID values found in the source table that occur within a timestamp of nine to two days prior (this procedure will be scheduled for a Monday for the previous week of Sunday through Saturday). If a given RcID value exists in the target table but not in the source table, I want to update a field called 'deleted bin' in the target table for that given RcID value. Where I'm getting confused (because I'm not well verse in SQL) is in framing out the statement... here's what I have come up with so far... any advice is greatly appreciated.
CREATE PROCEDURE CompareAndUpdateDeletedBin
AS
BEGIN
-- Update DeletedBin field in target table for RcIDs that exist in target but not in source
UPDATE target.recvCart
SET DeletedBin = 'x'
WHERE RcID IN (
SELECT source.RcID
FROM source.recvCart target
LEFT JOIN target.recvCart source ON source.RcID = target.RcID
WHERE source.RcID IS NULL);