Forum Stats

  • 3,853,171 Users
  • 2,264,187 Discussions
  • 7,905,272 Comments

Discussions

Use returned values in DML in a WITH clause without staging them

Rafiq D
Rafiq D Member Posts: 57 Blue Ribbon
edited Jan 11, 2016 5:54PM in Database Ideas - Ideas

In another RDBMS, I can perform multiple DMLs within the same WITH clause by passing the returned values from one DML into another without staging the data. Below is an example:

WITH insert_source AS(
       INSERT INTO trnxn_tab(col1,col2,col3,col4)
       VALUES(val1,val2,val3,val4)
       RETURNING col4,trxn_id
) UPDATE account SET last_activity_date =  sysdate, account_bal = (account_bal - insrt.col4), last_trxn_id = insrt.trxn_id
  FROM insert_source insrt
  WHERE account_number = insrt.account_number;

Even though this isn't as pretty as I would expect, it does allow me to do multiple DMLs by using the returned keys and values from one dml into another. I know there are workaround in Oracle but adding this will be cool. I will prefer the Oracle version avoids that from clause as below:

WITH insert_source AS(
       INSERT INTO trnxn_tab(col1,col2,col3,col4)
       VALUES(val1,val2,val3,val4)
       RETURNING col4,trxn_id
) UPDATE account SET (last_activity_date, account_bal, last_trxn_id) = (SELECT sysdate, (account_bal - insrt.col4) ,insrt.trxn_id FROM insert_source insrt)
  WHERE account_number = insrt.account_number;

Rafiq DMd. Budrul Hasan BhiuyanPravin Takpire
3 votes

Active · Last Updated