8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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...