I want to remove duplicate id data with following sql, but I am getting the following error then

gettin error ORA-01732: data manipulation operation not legal on this view

delete
from MV_JTZC_GBJG
where id in
      (select id from MV_JTZC_GBJG group by id having count(id) > 1)
  and rowid not in (select min(rowid)
                    from MV_JTZC_GBJG
                    group by id
                    having count(id) > 1);

CodePudding user response：

Looks like you're deleting from a (materialized, MV_...) view. If so, Oracle says

ORA-01732: data manipulation operation not legal on this view

Cause: An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.

Action: UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.

Which means that you should delete rows from underlying table(s), and not from the (materialized) view. Perhaps you could, if materialized view was created with the for update clause (used for replication).

On the other hand, maybe query - that was used to create that (materialized) view - isn't good and it causes duplicates to appear. In that case, fix the query (e.g. maybe some join conditions are missing).