June 22, 2006 at 9:29 am
I have 2 tables joined on a composite primary-foregin key relationship
table
VideoRelease
MuzeId INT PRIMARY KEY
RelNum SMALLINT PRIMARY KEY
this is the Foreign Key for table
ReleaseLanguage
MuzeId INT PRIMARY KEY
RelNum SMALLINT PRIMARY KEY
LangID SMALLINT PRIMARY KEY
I want to delete all records in ReleaseLanguage which are not in the VideoRelease table.
The join is successful -
SELECT * FROM ReleaseLanguage a
LEFT JOIN VideoRelease b
ON (a.MuzeId = b.MuzeId AND a.RelNum = b.RelNum )
WHERE (b.MuzeId IS NULL AND b.RelNum IS NULL)
A delete with a CheckSum also works -
DELETE FROM ReleaseLanguage WHERE CHECKSUM(MuzeID, RelNum ) =
(SELECT CHECKSUM(a.MuzeID, a.RelNum ) FROM ReleaseLanguage a
LEFT JOIN VideoRelease b
ON (a.MuzeId = b.MuzeId AND a.RelNum = b.RelNum )
WHERE (b.MuzeId IS NULL AND b.RelNum IS NULL))
But its a risky method because of the possibility of a collision - if for eg MuzeID=5 and PRelRefnum=3 it will have a checksum collision with MuzeID=3 and PRelRefnum=5
I need to delete because of the composite key relationships in Muze Video else I keet getting insert failed errors.
Do you have a cleaner TSQL syntax that will work instead of doing a checksum? Surrogate keys are NOT an option on these tables.
June 22, 2006 at 9:46 am
DELETE RL
FROM ReleaseLanguage AS RL
WHERE NOT EXISTS
(
SELECT *
FROM VideoRelease As VR
WHERE VR.MuzeId = RL.MuzeId
AND VR.RelNum = RL.RelNum
)
If a foreign key constraint exists, with ReleaseLanguage being dependant on records existing in VideoRelease, how did you end up with records where the key does not exist ? Was the FK constraint deleted/disabled at one point in time ?
June 22, 2006 at 10:08 am
This worked, thanks!
(BTW, I have a temporary staging database where I clean the relationships before loading the data into the final DB)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply