Delete on a composite key? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete on a composite key?

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 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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-22 : 11:27:05

This is enough, I think.

DELETE		ReleaseLanguage 
FROM		ReleaseLanguage rl
LEFT JOIN	VideoRelease vr ON vr.MuzeId = rl.MuzeId AND vr.RelNum = rl.RelNum
WHERE		vr.MuzeId IS NULL

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources