delete from two tables

[mrdance]

I have to tables connected with two field called vpid. The field id which is in the table bo_vp is the unique row. One row in bo_vpid can be connected to several rows in bo_vp.

What I'm trying to do is to delete a row in bo_vp by specifying "where id =". I want to delete the row in bo_vpid when all id's in bo_vp is deleted. Instead of deleting one id, checking if more id's exist with the same vpid and then if no id's with that vpid exist delete the row from the bo_vpid. I have tried this but it didn't work;

delete from (select * from bo_vp v, bo_vpid f where f.vpid = v.vpid) k where k.id = 140

Do you have any suggestions? Thank you!

/ Henrik

 

[johpje]

I see 2 ways to do this:

Just delete the records from bo_vp with

DELETE FROM bo_vp WHERE id = xxx;

and create a stored procedure to delete from bo_vpid if the last bo_vp for that id was deleted.

Or (maybe even better) create a function to delete and check in one go.

It's been awhile since i wrote some stored PL/SQL myself so i'm not able to give a clear example of these solutions. sorry. Maybe someone else can?

Regards
Johpje

 

[jee]

Another option is to specify a cascading delete in your table definition:

CREATE TABLE employee(
   employee_id  NUMBER(10) 
                CONSTRAINT pk_employee PRIMARY KEY
   dept_number  NUMBER (10)
                CONSTRAINT fk_emp_dept FOREIGN KEY
                REFERENCES department
                ON DELETE CASCADE
.
.
.
[\CODE]

Or use the ALTER TABLE syntax if the table already exists.

Now when you delete a row from the department table, any row that references that department in the employee table will also be deleted.

WARINING: You did not specify which version of Oracle you are using, but there is a problem in 8.1.6 with specifying ON DELETE CASCADE on tables that also have a DELETE trigger. This is fixed in 8.1.7.