Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
banditking
Starting Member
5 Posts |
Posted - 2006-06-22 : 11:22:24
|
I have 2 tables joined on a composite primary-foregin key relationshiptable VideoReleaseMuzeId INT PRIMARY KEY RelNum SMALLINT PRIMARY KEY this is the Foreign Key for tableReleaseLanguageMuzeId 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 aLEFT JOIN VideoRelease bON (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 aLEFT JOIN VideoRelease bON (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=5I 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. |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-22 : 11:27:05
|
This is enough, I think.DELETE ReleaseLanguage FROM ReleaseLanguage rlLEFT JOIN VideoRelease vr ON vr.MuzeId = rl.MuzeId AND vr.RelNum = rl.RelNumWHERE vr.MuzeId IS NULL Peter LarssonHelsingborg, Sweden |
|
|
|
|
|