Delete syntax for composite key join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Delete syntax for composite key join?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-11 : 11:18:03

Pras writes "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."

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-11 : 11:22:22

delete ReleaseLanguage
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)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources