asked on

Able to SELECT but not DELETE

Hello experts,

Thanks to the excellent help provided by johnsone a select statement has been crafted that
allows me to identify the data duplicates that need to be nixed.

SELECT *
FROM (SELECT PAYER_ID,
COMPONENT_ID,
LAST_UPDATE_DATE,
IS_OVERRIDE,
Row_number()
over (
PARTITION BY PAYER_ID, COMPONENT_ID
ORDER BY LAST_UPDATE_DATE ASC) RWN
FROM J3062_ACP_DEL_DUP_COMP_706
WHERE ( PAYER_ID, COMPONENT_ID ) IN ( ( 100542, 23 ),
( 109473, 367 ),
( 117273, 81 ),
( 100656, 794 ),
( 100083, 113 )
)
)
WHERE RWN = 1
and
IS_OVERRIDE != 1;

I have granted the user SELECT and DELETE on the object.
GRANT SELECT ON J3062_ACP_DEL_DUP_COMP_706 TO CCM;
GRANT DELETE ON J3062_ACP_DEL_DUP_COMP_706 TO CCM;

The SELECT Statement runs with positive result.

But when 'SELECT' is replaced with 'DELETE' SQL Developer returns
"Error at Command Line:1 Column:7
Error report:
SQL Error: ORA-00903: invalid table name
00903. 00000 - "invalid table name"
*Cause:
*Action:"

The fact that the query errors out on the first line would lead
one to believe that it is the DELETE statement that is the fly
in the ointment. Also when the "invalid table name" is
returned quite often it has nothing to do with the table name
but rather privileges on the table.

Should not the GRANT and DELETE privileges granted
allow the query to operate on the table?

Thanks.

Allen Pitts, Dallas Texas

PortletPaul

It quite often isn't possible to just substitute a "delete" where one previously had "select" (i.e. what you are trying to delete from is a "derived table", not a real table)

Try this approach :

DELETE FROM J3062_ACP_DEL_DUP_COMP_706
WHERE PAYER_ID IN (
                SELECT
                        PAYER_ID
                FROM (
                        SELECT
                                PAYER_ID
                              , IS_OVERRIDE
                              , ROW_NUMBER()
                                OVER (
                                PARTITION BY PAYER_ID, COMPONENT_ID
                                ORDER BY LAST_UPDATE_DATE ASC) RWN
                        FROM J3062_ACP_DEL_DUP_COMP_706
                        WHERE  ( PAYER_ID, COMPONENT_ID ) IN ( ( 100542, 23 ),
                                                               ( 109473, 367 ),
                                                               ( 117273, 81 ),
                                                               ( 100656, 794 ),
                                                               ( 100083, 113 )
                                                              )
                ) 
                WHERE RWN = 1
                AND IS_OVERRIDE <> 1
        )

Open in new window

{+edit}I know I will seem like a fusspot but the SQL standard for not equal is "<>" change it back if you really have to but to my eyes <> is a better syntax.

ASKER CERTIFIED SOLUTION