Delete on a composite key?

[CodeDigger]

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.

 

[PHV]

Perhaps something like this ?
DELETE FROM ReleaseLanguage
WHERE MuzeID || ',' || RelNum NOT IN (
SELECT MuzeID || ',' || RelNum FROM VideoRelease)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CodeDigger]

Thanks for replying. I don't understand this syntax?

When I run it I get these errors

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '|'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '|'.

 

[PHV]

I gave you the ANSI SQL syntax.
Replace || with the concatenation operator of your SQL dialect.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CodeDigger]

The Bitwise OR operator returns 22,840 rows

SELECT * FROM ReleaseLanguage WHERE MuzeId | RelRnum NOT IN
( SELECT MuzeId | RelRnum FROM VideoRelease)

whereas the checksum method returned 60,000 + rows


This bitwise operator is basically the same route as the checksum isnt it? (possibly with less chance of collision?)

I believe I have the + as the concat operator (default installation) but it didnt work anyways.

 

[CodeDigger]

This syntax works -


DELETE RL
FROM ReleaseLanguage AS RL
WHERE NOT EXISTS
(
SELECT *
FROM VideoRelease As VR
WHERE VR.MuzeId = RL.MuzeId
AND VR.RelNum = RL.RelNum
)

 

[PHV]

Seems you didn't understand my posts ...
T-SQL syntax:
DELETE FROM ReleaseLanguage
WHERE MuzeID [!]+[/!] ',' [!]+[/!] RelNum NOT IN (
SELECT MuzeID [!]+[/!] ',' [!]+[/!] RelNum FROM VideoRelease)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CodeDigger]

Oh... that is clever! I guess this works just as well.

 

[r937]

what PVH was trying to say is that this is the ANSI SQL forum

yes, ANSI SQL is the SQL standard, but that does not mean that all databases support it

you should ask database-specific questions in the forum for your database, so if you're using TSQL then you should be in the microsoft sql server forum (forum183)

r937.com | rudy.ca