April 22, 2005 at 1:42 pm
I am trying to write script to delete records from 2 tables at the same time.
The tables have a 1-many relationship. Table#1 has a FK to Table#2 with Lastname. How would you delete all records Table#1 with state value WV and the cooresponding records in Table#2 ?
For example
Table#1 Lastname, Firstname, State
Adams, John, WV
Smith, Jane, PA
Table#2 Lastname, Class, Grade
Adams, Biology, B
Adams, Chemistry, C
April 22, 2005 at 1:55 pm
BEGIN TRANSACTION
Delete from Table#1 Where lastName='Adams'
if @@error <> 0 goto QuitwithRollBack
Delete From Table#2 Where lastName='Adams'
If @@error <> 0 goto QuitwithRollBack
COMMIT TRANSACTION
RETURN (0)
QuitWithRollBack:
If @@TRancount > 0 ROLLBACK TRANSACTION
RETURN (1)
* Noel
April 22, 2005 at 2:00 pm
nope
I'm looking for ONE statement to do it
I'm having problems with the INNER JOIN clause
April 22, 2005 at 2:07 pm
It's impossible to do it in 1 query.
April 22, 2005 at 2:09 pm
What about using a "cascaded" DELETE ?
Referential Integrity is the real pain.
April 22, 2005 at 3:27 pm
You can set up cascading on delete or on change, but it's dangerous...
Bill King
April 22, 2005 at 3:56 pm
Danger is my middle name. How about:
ALTER TABLE Table1 ADD CONSTRAINT FK_Table1_Table2 FOREIGN KEY
REFERENCES Table2 ( LastName )
ON DELETE CASCADE
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply