Complicate delete-statement involving two tables

[Swaq]

Or, at least, I find it complicated :-)

Hopefully it is not that hard but here goes:

I have two tables:
Persons and Relations

Persons contain personal data such as person_ID, name, adresse, etc.
and a special-status-code (a person with this code set = 1 is a VIP)

Relations contains a person_ID and a vip_ID (the vip_ID is in fact a
person_ID - it is just a person with his special-status-code = 1). So
the relation table is used to register who a VIP knows (his contacts).

My problem is when I delete a VIP.

I have no problems just deleting the VIP from the Persons-table, but I
also need to delete all of his contacts (not only from the
Relations-table but also from the Persons-table).

I think this might be part of the solution:
ALTER PROCEDURE Delete_Vip
(
@Vip_ID INT /* ID of the VIP to be deleted */
)

AS

DELETE
FROM Persons p
WHERE r.Vip_ID = @Vip_ID
AND p.Person_ID NOT IN (
SELECT * FROM Relations r WHERE Vip_ID <> @Vip_ID
)

This does not work, but the idea in the subquery is to NOT delete
persons that are known by other VIP's than the one I am deleting. In
other words I am only interested in the VIP's exclusive contacts.

I am also quite sure that the above SQL-statement does not remove the
VIP's posts in the Relations-table.
I am open to suggestions :-)

Have a splendid weekend!

[Bill Karwin]

Swaq wrote:

I have no problems just deleting the VIP from the Persons-table, but I
also need to delete all of his contacts (not only from the
Relations-table but also from the Persons-table).

I think this might be part of the solution:
ALTER PROCEDURE Delete_Vip
(
@Vip_ID INT /* ID of the VIP to be deleted */
)

Your first problems is that you are using the Microsoft SQL Server
syntax: '@' to introduce variables. MySQL syntax is different. Refer
to the MySQL 5.x documentation for examples of proper procedure language
syntax. Also, I assume you're aware that procedures are only available
in MySQL 5, and this is still in a Beta state -- not recommended for
production use.

I also recommend you name your procedure's input parameter something
different from your field name, to avoid confusion. For instance,
"Vip_ID_to_delete".

Does the following get you closer to your solution?

// Delete the VIP and all his relationships.
DELETE FROM Persons, Relations
USING Persons P JOIN Relations R ON (P.Person_ID = R.Vip_ID)
WHERE P.Person_ID = Vip_ID_to_delete;

// Then delete all persons for whom no VIP relationship exists;
// skip those persons for whom a relationship still exists, because
// it must be for a different VIP, following the above operation.
DELETE FROM Persons
USING Persons P LEFT OUTER JOIN Relations R
ON (P.Person_ID = R.Vip_ID)
WHERE R.Vip_ID IS NULL;

Regards,
Bill K.