CODEWITHSUNDEEP
sqldatabaseoracle-database
Gayan Kavirathne
Gayan Kavirathne

Reputation: 3237

Oracle UPDATE as a inner query inside a where clause

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 want the update to execute on within the WHERE clause. This is to be used for a ethical hacking scenario.

Upvotes: 2

Views: 353

Answers (2)

sandman
sandman

Reputation: 2118

No you will get ORA-14551: cannot perform a DML operation inside a query

Cause: DML operation like insert, update, delete or select-for-update cannot be performed inside a query or under a PDML slave.

Action: Ensure that the offending DML operation is not performed or use an autonomous transaction to perform the DML operation within the query or PDML slave.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31686

There is no way to do it the way you describe. You can use the SQL%ROWCOUNT implicit cursor attribute in a PL/SQL block as shown.

 DECLARE
 ct NUMBER(5);
BEGIN
  UPDATE table_B SET column3 = 20 WHERE column3 = 40;
  ct := SQL%ROWCOUNT;
  INSERT INTO table_A
  SELECT 1 column1 , 2 column2 FROM DUAL CONNECT BY LEVEL <= ct;
END;
/

Upvotes: 2

Related Questions