Oracle trigger if updating column under 18 single dating

Rated 4.95/5 based on 921 customer reviews

This article introduces the new MERGE SQL command (sometimes referred to as "UPSERT").

MERGE is a DML command that enables us to optionally update or insert data into a target table, depending on whether matching records already exist.

In versions prior to 9i, we would have to code this scenario either in separate bulk SQL statements or in PL/SQL.

We will compare MERGE to these methods later in this article.

We could speed up the latter by using bulk processing, but we wouldn't be able to achieve a reduction of two-thirds required to match the MERGE. This means that for each source row, Oracle needs to be able to identify a single target record for update.

The simplest method of ensuring that the MERGE is key-preserved is to join source and target according to the primary key of the target.

Execution Plan ---------------------------------------------------------- 0 MERGE STATEMENT Optimizer=CHOOSE (Cost=194 Card=86889 Bytes=9557790) 1 0 MERGE OF 'TARGET_TABLE' 2 1 VIEW 3 2 HASH JOIN (OUTER) (Cost=194 Card=86889 Bytes=7038009) 4 3 TABLE ACCESS (FULL) OF 'SOURCE_TABLE' (Cost=46 Card=86889 Bytes=2867337) 5 3 TABLE ACCESS (FULL) OF 'TARGET_TABLE' (Cost=24 Card=18950 Bytes=909600) We can see that Oracle performs an outer join between the source dataset and target table (in our case we happen to have a hash join).

This creates a non-mergeable view (this is an unfortunate coincidence in terminology) that is applied back to the target table.

We will revert to the key-preserved join and this time our MERGE should be successful.We can see that MERGE performed less well than our two-part SQL solution; with it taking over twice as long. We can repeat the test against a typical PL/SQL-coded merge (common in older applications).We will replace the two-part SQL solution with a PL/SQL loop that will attempt an update first and insert only if the update affects no rows.We can demonstrate what happens if we cannot ensure key-preservation by modifying our MERGE to join on a column other than that of the primary key.MERGE 2 INTO target_table tgt 3 USING source_table src 4 ON ( src.object_name = tgt.object_name ) 5 WHEN MATCHED 6 THEN 7 UPDATE 8 SET tgt.object_type = src.object_type 9 WHEN NOT MATCHED 10 THEN 11 INSERT ( tgt.object_id 12 , tgt.object_name 13 , tgt.object_type ) 14 VALUES ( src.object_id 15 , src.object_name 16 , src.object_type ); This error message is slightly confusing.

Leave a Reply