Delete query (more relation between table)

[edustreet]

Hi Everyone,

I have a problem to delete record in Table A, since the table have relation to Table B. For example,

Table A = StudentMarks
StudentID SubjectMarks
1 50
2 77
3 100

Table B = StudentData
StudentID Name Class
1 John 1A
2 Calvin 1A
3 Kelly 2B

* I want to delete the StudentMarks in class 1A. The "StudentID" is the foreign key which related to TableB to get the "Class".

Is it possible to do that? Any help will be appreciate.

Calvin

[Hack]

So, you want to delete the marks, but leave the StudendID in place with all the remaining fields blank?

[edustreet]

No, I want to delete the whole record including the student ID. This two record are totally delete.

1 50
2 77

[Slope]

Try:

DELETE FROM TableA
WHERE EXISTS
(
SELECT *
FROM TableB
WHERE TableA.StudentID = TableB.StudentID
AND TableB.Class = '1A'
)

[edustreet]

It works

Thank you very much