Delete SQL stmt

[udit99]

I need to have a SQL stmt wherein if a Publisher Record is Deleted then all related Title Records( holding the Publisher FK) are also Deleted . In MS SQL 7.0 I used to have a stored proc which simply consisted of 2 seperate DELETE SQL stmts. Now Im using Access and Iv tried the following...none work

1.DELETE FROM Publisher WHERE Publisher.PID='123'
DELETE FROM Titles WHERE Titles.PID='123'

2. DELETE FROM Publisher,Titles WHERE Publisher.PID='123' and Titles.PID='123'

3. DELETE FROM Publisher,Titles WHERE Publisher.PID=Titles.PID and Publisher.PID='123'

[BruceG]

You must delete the records with the FK first. Try your #1 again, but in reverse order. #2 and #3 will not work.

[techgnome]

OR.... you can set up a foreign key constraint w/ a cascade delete on it.
Then you only have to delete the parent, and all related FKeys will go w/ it.

[udit99]

This gives a Syntax Error

DELETE FROM Titles WHERE Titles.PID=2001
DELETE FROM Publisher WHERE Publisher.PID=2001


Syntax Error(Missing Operator) in Query expression Titles WHERE Titles.PID=2001 DELETE FROM Publisher WHERE Publisher.PID=2001


1. I also tried removing the FROM
2. I tried putting the 2001 as '2001'
3. I tried Removing the 'Titles.' and the 'Publisher.' from the PID=2001

[Feetstink]

Your sql statement is DELETE FROM Titles WHERE Titles.PID=2001
DELETE FROM Publisher WHERE Publisher.PID=2001? Are you running two separate queries? Do you get the same error when you run them individually? i guess i just don't understand that error.

Originally posted by udit99
This gives a Syntax Error

DELETE FROM Titles WHERE Titles.PID=2001
DELETE FROM Publisher WHERE Publisher.PID=2001


Syntax Error(Missing Operator) in Query expression Titles WHERE Titles.PID=2001 DELETE FROM Publisher WHERE Publisher.PID=2001


1. I also tried removing the FROM
2. I tried putting the 2001 as '2001'
3. I tried Removing the 'Titles.' and the 'Publisher.' from the PID=2001

[kirankumar]

Just try this

If the datatype for column PID is number then

Delete * from titles where PID=2001
Delete * from Publisher where PID=2001

else if the datatype of PID is text then

Delete * from titles where PID='2001'
Delete * from Publisher where PID='2001'

[udit99]

Actually the two SQL stmts individually work fine but combined , they give a Syntax error...anyways, I looked for that Cascade Delete feature and I guess this solves my problem without going into the Double SQL stmt bit.