| Help please! DELETE FROM [message #374896] |
Tue, 10 July 2001 07:49  |
Stephen
Messages: 26
Registered: January 2000
|
Junior Member |
|
|
We have in our database 2 tables. A and B. In B are child-records of A stored in a 1-to-many relation. Normally we should have a foreign key in B with the key of A, but we haven't.
Now I would like to do a delete in 1 action like DELETE CASCADE where all child-records of a parent are deleted when the parent is deleted.
Is it possible to do that in stead of first deleting the child records and then the parent-record in 2 actions?
|
|
|
|
| Re: Help please! DELETE FROM [message #374897 is a reply to message #374896] |
Tue, 10 July 2001 08:30  |
Atul Kotkar
Messages: 2
Registered: July 2001
|
Junior Member |
|
|
Action 1:
DELETE FROM B
WHERE B.COL1 IN
(SELECT COL1 FROM A)
Action 2:
DELETE FROM A
WHERE A.COL1 IN
(SELECT COL1 FROM B)
Hi, Stephen!
try this with proper conditions of matching columns in both tables. I tried this on DEPT & EMP
tables. So it must work, hope so.
Regards,
Atul.
|
|
|
|
| Re: Help please! DELETE FROM [message #374898 is a reply to message #374896] |
Tue, 10 July 2001 09:01 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
It is still not so late. You can add a foreign key constraint to the table B that references the primary key of table A and then delete any row in the first table A. It automatically deletes the corresponding rows in the second table B. After deletion, if you dont want the foreign key constraint to be attached to the second table, then remove it. Both of these, addition and removing of these constraints can be done with ALTER command.
Ex:
ALTER TABLE table_B ADD CONSTRAINT foreignkeyname FOREIGN KEY (pno) REFERENCES table_A(pno) ON DELETE CASCADE;
DELETE FROM table_a WHERE condition...
ALTER TABLE TABLE_B DROP CONSTRAINT foreignkeyname;
Hope this helps to resove your problem.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
