Question on deleting records from a table 1

[EdRev]

I read from an Oracle source book that I can not delete a record from a query with a join constructs.

I have two tables:

base_tbl

unit_id number(8)
jan number(8)
feb number(8)
...
dec number(8)

unit_tbl

mem_id number(8)
mem_name char(5)

I want to delete the records returned by this query:

select unit_id,jan,feb,mar...dec from base_tbl,unit_tbl where base_tbl.unit_id = unit_tbl.mem_id and substr(unit_tbl.mem_name,1,1) = 'M'.

How do i go about doing this.

Any help will be greatly appreciated.

 

[Dagon]

Delete from which table ? Base_tbl or unit_tbl or both ?

 

[EdRev]

From the base_tbl.

 

[jag14612]

you need to use a subquery to do this:

delete from base_tbl
where unit_id in
(select unit_id
from base_tbl,unit_tbl
where base_tbl.unit_id=unit_tbl.mem_id
and substr(unit_tbl.mem_name,1,1) = 'M');

the important thing is seperating out the subquery to return an id. This assumes the tables are normalized and the id fields are really id fields.

hope this helps!