In my understanding, you want to do........
From these two sample data
------------------------- Commands Entered -------------------------
SELECT * FROM utSystem.t_Filt er_Results
ORDER BY 1;
--------------------------------------------------------------------
CLUBID FILTERID MEMBERID
----------- ----------- -----------
100 50 1
200 50 2
300 50 3
400 40 3
4 record(s) selected.
------------------------- Commands Entered -------------------------
SELECT * FROM club.vc_9;
--------------------------------------------------------------------
ID LOCATION# ENTERING_DATE LEAVING_DATE
----------- --------- ------------- ------------
1 Z?rich 2005-10-19 -
2 ZXrich 2005-10-19 -
3 Z?rich 2005-10-14 -
4 Z?rich 2005-10-29 2005-11-03
4 record(s) selected.
Get following result
------------------------- Commands Entered -------------------------
SELECT * FROM utSystem.t_Filt er_Results;
--------------------------------------------------------------------
CLUBID FILTERID MEMBERID
----------- ----------- -----------
300 50 3
9 50 4
100 50 1
3 record(s) selected.
If so, one idea is DELETE and INSERT separately:
DELETE FROM utSystem.t_Filt er_Results Res
WHERE NOT EXISTS
(SELECT *
FROM club.vc_9 Vc9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS
NULL))
AND Res.MemberID = Vc9.ID
AND Res.FilterID = 50
);
INSERT INTO utSystem.t_Filt er_Results
WITH Fil AS (
SELECT 9 as clubid, 50 as filterid, ID as MemberID
FROM club.vc_9 Vc9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE IS NULL))
)
SELECT Fil.ClubID, Fil.FilterID, Fil.MemberID
FROM Fil
WHERE NOT EXISTS
(SELECT *
FROM utSystem.t_Filt er_Results Res
WHERE Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID
);
My another idea using MERGE is something complicated and I suspect
performance. But, I'll show you it for your reference:
MERGE INTO utSystem.t_Filt er_Results AS Res
USING (SELECT 9 as clubid, COALESCE(R.Filt erID, 50) as filterid
, COALESCE(R.Memb erID, V.ID) as MemberID
, CASE
WHEN V.ID IS NULL THEN
2
WHEN R.MemberID IS NULL
AND R.FilterID IS NULL THEN
1
ELSE 0
END AS Matched
FROM (SELECT *
FROM club.vc_9
WHERE ((LOCATION# = 'Z?rich'))
AND (ENTERING_DATE <= CURRENT DATE)
AND ((LEAVING_DATE > CURRENT DATE) OR (LEAVING_DATE
IS NULL))
) AS V
FULL OUTER JOIN
utSystem.t_Filt er_Results AS R
ON R.MemberID = V.ID
AND R.FilterID = 50
) AS Fil
ON Res.MemberID = Fil.MemberID
AND Res.FilterID = Fil.FilterID
AND 2 = Fil.Matched
WHEN MATCHED THEN
DELETE
WHEN NOT MATCHED AND 1 = Fil.Matched THEN
INSERT (ClubID, FilterID, MemberID)
VALUES (Fil.ClubID, Fil.FilterID, Fil.MemberID)
;
