Search code examples
sqlsql-delete

Deleting data from two linked tables

I've got two tables called Patient and Account. Patient has PatientID as it's PrimaryKey. Account has AccountID as PK and PatientID is a Foreign Key and also has treatmentDate as a tuple.

I want to delete all patients that have not had an account since 2005. this is what I've got:

DELETE FROM PATIENT
WHERE PATIENTID IN (
  select account.PatientID
  from ACCOUNT
  where Treatmentdate < '01-JAN-2005' );
DELETE FROM ACCOUNT
WHERE PATIENTID IN (
  select account.PatientID
  from ACCOUNT
  where Treatmentdate < '01-JAN-2005' );

is there any other way in which I can do this?

Solution

  • I'd rather use the 

    ON DELETE CASCADE

    option in the Foreign Key PatientID of the ACCOUNT table.

    In this case you wouldn't need the second query.

    See for reference (there's plenty of info in the net): SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?