CODEWITHSUNDEEP
sqlsql-delete
Pindo
Pindo

Reputation: 1625

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?

Upvotes: 0

Views: 160

Answers (1)

ᗩИᎠЯƎᗩ
ᗩИᎠЯƎᗩ

Reputation: 2132

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?

Upvotes: 1

Related Questions