Selecting values while merging data

When you update existing data and insert new data in a single merge operation, you can select values from those rows at the same time.

About this task

You can select values from rows that are being merged by specifying the MERGE statement in the FROM clause of the SELECT statement. When you merge one or more rows into a table, you can retrieve:
  • The value of an automatically generated column such as a ROWID or identity column
  • Any default values for columns
  • All values for a merged row, without specifying individual column names
  • Calculated values based on the changes to merged rows
Specify the FINAL TABLE clause with SELECT FROM MERGE statements. The FINAL TABLE consists of the rows of the table or view after the merge occurs.
Example: Suppose that you need to input data into the STOCK table, which contains company stock symbols and stock prices from your stock portfolio. Some of your input data refers to companies that are already in the STOCK table; some of the data refers to companies that you are adding to your stock portfolio. If the stock symbol exists in the SYMBOL column of the STOCK table, you need to update the PRICE column. If the company stock symbol is not yet in the STOCK table, you need to insert a new row with the stock symbol and the stock price. Furthermore, you need to add a new value DELTA to your output to show the change in stock price.
Suppose that the STOCK table contains the data that is shown in Table 1.
Table 1. STOCK table before SELECT FROM MERGE statement
SYMBOL PRICE
XCOM 95.00
YCOM 24.50
Now, suppose that :hv_symbol and :hv_price are host variable arrays that contain updated data that corresponds to the data that is shown in Table 1. Table 2 shows the host variable data for stock activity.
Table 2. Host variable arrays of stock activity
hv_symbol hv_price
XCOM 97.00
NEWC 30.00
XCOM 107.00

NEWC is new to the STOCK table, so its symbol and price need to be inserted into the STOCK table. The rows for XCOM in Table 2represent changed stock prices, so these values need to be updated in the STOCK table. Also, the output needs to show the change in stock prices as a DELTA value.

The following SELECT FROM MERGE statement updates the price of XCOM, inserts the symbol and price for NEWC, and returns an output that includes a DELTA value for the change in stock price. 
SELECT SYMBOL, PRICE, DELTA FROM FINAL TABLE
  (MERGE INTO STOCK AS S INCLUDE (DELTA DECIMAL(5,20)
   USING ((:hv_symbol, :hv_price) FOR :hv_nrows ROWS) AS R (SYMBOL, PRICE)
   ON S.SYMBOL = R.SYMBOL
     WHEN MATCHED THEN UPDATE SET 
       DELTA = R.PRICE - S.PRICE, PRICE=R.PRICE
     WHEN NOT MATCHED THEN INSERT 
       (SYMBOL, PRICE, DELTA) VALUES (R.SYMBOL, R.PRICE, R.PRICE)  
NOT ATOMIC CONTINUE ON SQLEXCEPTION);

The INCLUDE clause specifies that an additional column, DELTA, can be returned in the output without adding a column to the STOCK table. The UPDATE portion of the MERGE statement sets the DELTA value to the differential of the previous stock price with the value set for the update operation. The INSERT portion of the MERGE statement sets the DELTA value to the same value as the PRICE column.

After the SELECT FROM MERGE statement is processed, the STOCK table contains the data that is shown in Table 3.
Table 3. STOCK table after SELECT FROM MERGE statement
SYMBOL PRICE
XCOM 107.00
YCOM 24.50
NEWC 30.00
The following output of the SELECT FROM MERGE statement includes both updates to XCOM and a DELTA value for each output row. 
SYMBOL    PRICE      DELTA
=============================
XCOM       97.00      2.00
NEWC       30.00     30.00
XCOM      107.00     10.00
Parent topic: Inserting data and updating data in a single operation