Delete Operation to delete data from multiple left outer join

Question

DELETE mp.*, rp.*, per.*, pv.*
FROM my_privilege mp 
LEFT JOIN ro_priv rp ON rp.privilege_id IN (privilege_id_rep)
LEFT JOIN person_priv per ON per.person_priv_id IN (privilege_id_rep)
LEFT JOIN privilege pv ON pv.privilege_id IN (privilege_id_rep)
WHERE (logino_id = p_int_logino_id);

Above code is delete operation which is in mysql ,I need to convert same to oracle 12c database. Could you please help me out

As Oracle will not support multiple deletes in a single query.

I need the approach of how to do delete operation when we have

delete from table
left join table1
left join table2
left join table3
left join table4
where condition

operations

Unfortunately for you, the "above answer", i.e. separate DELETE statements per table, gives the only valid Oracle syntax. — APC
– APC, Commented Jan 28, 2019 at 14:19

Littlefoot · Accepted Answer · 2019-01-28 07:32:40Z

1

As far as I can tell, in Oracle you're supposed to delete table-by-table, i.e. you can't affect multiple tables in a single DELETE statement. Therefore, that would be

delete from my_privilege mp
  where mp.some_id in (select x.some_id from some_other_table x ...);

delete from person_priv per
  where per.some_id in (select x.some_id from some_other_table x ...);

etc.

If there are foreign key constraints, see whether ON DELETE CASCADE is used so that deleting a master row deletes all details (in a single statement).

answered Jan 28, 2019 at 7:32

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sudhakar K Over a year ago

Yes but here i have multiple left join

Sudhakar K Over a year ago

So then I need delete from my_privilege mp where mp.some_id in ( HERE I NEED TO have all the left joins);

Collectives™ on Stack Overflow

Delete Operation to delete data from multiple left outer join

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related