8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Updates Based on Queries

This article describes how a table can be updated using data from another table.

Related articles.

Setup

The DEST_TAB table contains 10,000 rows. The SOURCE_TAB table contains 5,000 rows, each of which has a matching key value with a row from the DEST_TAB table, but different data in the CODE and DESCRIPTION columns.

DROP TABLE dest_tab PURGE;
DROP TABLE source_tab PURGE;

CREATE TABLE dest_tab AS
SELECT level AS id,
       'CODE' || level AS code,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 10000;

ALTER TABLE dest_tab ADD CONSTRAINT dest_tab_pk PRIMARY KEY (id);


CREATE TABLE source_tab AS
SELECT level AS id,
       'CODE' || (level*10) AS code,
       'Updated description for ' || level AS description
FROM   dual
CONNECT BY level <= 5000;

ALTER TABLE source_tab ADD CONSTRAINT source_tab_pk PRIMARY KEY (id);

EXEC DBMS_STATS.gather_table_stats(USER, 'dest_tab');
EXEC DBMS_STATS.gather_table_stats(USER, 'source_tab');

At this point we can see none of the values in the DESCRIPTION column of the DEST_TAB table contain the word "Updated".

SELECT COUNT(*)
FROM   dest_tab
WHERE  description LIKE 'Updated%';

  COUNT(*)
----------
         0

SQL>

The aim is to update the rows in the DEST_TAB table with the data from the SOURCE_TAB table.

Subquery Method

The first option is to do an update of the DEST_TAB table using a subquery to pull the correct data from the SOURCE_TAB table. Notice the EXISTS predicate to exclude rows from the DEST_TAB table with no matching row in the SOURCE_TAB table. Without this, the unmatched rows will have their values set to NULL.

UPDATE dest_tab tt
SET    (tt.code, tt.description) = (SELECT st.code, st.description
                                    FROM   source_tab st
                                    WHERE  st.id = tt.id)
WHERE  EXISTS (SELECT 1
               FROM   source_tab
               WHERE  id = tt.id);

5000 rows updated.

SQL>


SELECT COUNT(*)
FROM   dest_tab
WHERE  description LIKE 'Updated%';

  COUNT(*)
----------
      5000

SQL>

ROLLBACK;

The execution plan for the current data volume is shown below.

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                |  4999 |   185K| 30013  (34)| 00:06:01 |
|   1 |  UPDATE                      | dest_tab       |       |       |            |          |
|   2 |   NESTED LOOPS SEMI          |                |  4999 |   185K|    19   (6)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | dest_tab       | 10000 |   332K|    18   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | source_tab_PK  |  2500 | 10000 |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| source_tab     |     1 |    43 |     2   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | source_tab_PK  |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"="TT"."ID")
   6 - access("ST"."ID"=:B1)

If the workload is sufficiently large and the server can cope with the extra workload, the PARALLEL hint can be used to make this run in parallel.

UPDATE /*+ PARALLEL(8) */ dest_tab tt
SET    (tt.code, tt.description) = (SELECT st.code, st.description
                                    FROM   source_tab st
                                    WHERE  st.id = tt.id)
WHERE  EXISTS (SELECT 1
               FROM   source_tab
               WHERE  id = tt.id);

Inline View Method

The second option is to join the two tables as an inline view and base the update on that.

UPDATE (SELECT tt.id,
               tt.code,
               tt.description,
               st.code AS st_code,
               st.description AS st_description
        FROM   dest_tab tt, source_tab st
        WHERE  tt.id = st.id) ilv
SET    ilv.code = ilv.st_code,
       ilv.description = ilv.st_description;

5000 rows updated.

SQL>


SELECT COUNT(*)
FROM   dest_tab
WHERE  description LIKE 'Updated%';

  COUNT(*)
----------
      5000

SQL>

ROLLBACK;

The execution plan for the current data volume is shown below.

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |             |  5000 |   375K|    31   (4)| 00:00:01 |
|   1 |  UPDATE             | dest_tab    |       |       |            |          |
|*  2 |   HASH JOIN         |             |  5000 |   375K|    31   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| source_tab  |  5000 |   209K|    12   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| dest_tab    | 10000 |   332K|    18   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TT"."ID"="ST"."ID")

If the workload is sufficiently large and the server can cope with the extra workload, the PARALLEL hint can be used to make this run in parallel.

UPDATE /*+ PARALLEL(8) */
       (SELECT tt.id,
               tt.code,
               tt.description,
               st.code AS st_code,
               st.description AS st_description
        FROM   dest_tab tt, source_tab st
        WHERE  tt.id = st.id) ilv
SET    ilv.code = ilv.st_code,
       ilv.description = ilv.st_description;

The number of rows updated can be altered by adding a WHERE clause to either the inline view or the main update statement.

-- Rows limited by the inline view.
UPDATE (SELECT tt.id,
               tt.code,
               tt.description,
               st.code AS st_code,
               st.description AS st_description
        FROM   dest_tab tt, source_tab st
        WHERE  tt.id = st.id
        AND    st.id  <= 2500) ilv
SET    ilv.code = ilv.st_code,
       ilv.description = ilv.st_description;

-- Rows limited by the WHERE clause of the UPDATE.
UPDATE (SELECT tt.id,
               tt.code,
               tt.description,
               st.code AS st_code,
               st.description AS st_description
        FROM   dest_tab tt, source_tab st
        WHERE  tt.id = st.id) ilv
SET    ilv.code = ilv.st_code,
       ilv.description = ilv.st_description
WHERE  ilv.id  <= 2500;

MERGE Statement Method

The third option is to use the MERGE statement, omitting the WHEN NOT MATCHED clause as it is not needed.

MERGE INTO dest_tab tt
    USING source_tab st
    ON (tt.id = st.id)
  WHEN MATCHED THEN
    UPDATE SET tt.code = st.code,
               tt.description = st.description;

5000 rows merged.

SQL>


SELECT COUNT(*)
FROM   dest_tab
WHERE  description LIKE 'Updated%';

  COUNT(*)
----------
      5000

SQL>

ROLLBACK;

The execution plan for the current data volume is shown below.

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |             |  5000 |   429K|    31   (4)| 00:00:01 |
|   1 |  MERGE               | dest_tab    |       |       |            |          |
|   2 |   VIEW               |             |       |       |            |          |
|*  3 |    HASH JOIN         |             |  5000 |   375K|    31   (4)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| source_tab  |  5000 |   209K|    12   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| dest_tab    | 10000 |   332K|    18   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TT"."ID"="ST"."ID")

If the workload is sufficiently large and the server can cope with the extra workload, the PARALLEL hint can be used to make this run in parallel.

MERGE /*+ APPEND PARALLEL(8) */ INTO dest_tab tt
USING source_tab st
ON (tt.id = st.id)
WHEN MATCHED THEN
  UPDATE SET tt.code = st.code,
             tt.description = st.description;

Remember, you can use queries as the source of a MERGE statement. The query can include a WHERE clause to limit the number of rows updated.

MERGE INTO dest_tab tt
    USING (SELECT * FROM source_tab WHERE id <= 2500) st
    ON (tt.id = st.id)
  WHEN MATCHED THEN
    UPDATE SET tt.code = st.code,
               tt.description = st.description;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.