Oracle UPDATE / INSERT whithin a where clause

oracle

I want to insert some values to a table based on the number of updates made by the same query.

An example queries I tried is as follows.

SELECT column1 column2 
  FROM table_A 
 WHERE (UPDATE table_B 
           SET column3= "?" 
         WHERE column3 = "?" 
       RETURN "UPDATED ROW COUNT") > 10 

There can be many other ways to do this. But I explicitly won't the update to execute on within the Where clause. This Is to be used for a ethical hacking scenario.

Best Answer

No, that is not possible. You will get an exception.

You can try with this:

CREATE TABLE TABLE_A (column1 NUMBER, column2 NUMBER);
CREATE TABLE TABLE_B (column3 NUMBER);

CREATE OR REPLACE FUNCTION UpdateTableB RETURN NUMBER AS
BEGIN
    UPDATE TABLE_B SET column3 = column3 * 2 
    WHERE column3 <= 10;
    RETURN SQL%ROWCOUNT;
END;
/

INSERT INTO TABLE_B SELECT LEVEL FROM dual CONNECT BY LEVEL < 20;
commit;

SELECT column1, column2 
FROM TABLE_A 
WHERE UpdateTableB > 10; 

ORA-14551: cannot perform a DML operation inside a query