Deleting data from two linked tables

Question

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?

found this [question](http://stackoverflow.com/questions/485581/generate-delete-statement-from-foreign-key-relationships-in-sql-2008/2677145#2677145) — Wietze314, Sep 10 '13 at 13:56

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

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?

edited May 23 '17 at 11:58

Community

1
1

answered Sep 10 '13 at 14:47

ᗩИᎠЯƎᗩ

2,122
5
29
41

Deleting data from two linked tables

1 Answers1