SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Delete on a composite key?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

banditking
Starting Member

Canada
5 Posts

Posted - 06/22/2006 :  11:22:24  Show Profile  Reply with Quote
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

Sweden
30217 Posts

Posted - 06/22/2006 :  11:27:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 06/22/2006 11:29:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000