UPSERTing and Logging What Happens

In the SQL world, the early name for a combined INSERT and UPDATE statement was "UPSERT".  It's a nifty one-stop way to update values when rows are already present and insert new rows when nothing previously existed. The SQL 2003 standard called the command MERGE because it merges two data sources together, either adding missing rows to the target table, or updating existing rows in the target with new values.

Oracle 9i introduced MERGE, and it was enhanced in Oracle 10g to allow not only the combination of INSERT and UPDATE commands, but also DELETE.

The example given in the Oracle SQL documentation is:

   MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN
     UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN
     INSERT (D.employee_id, D.bonus)
       VALUES (S.employee_id, S.salary*0.1)
       WHERE (S.salary <= 8000);
See full MERGE syntax for the full example.

Another feature introduced in Oracle 10g for MERGE, INSERT, UPDATE and DELETE is the error logging clause.  This "lets you capture DML errors and the log column values of the affected rows and save them in an error logging table."  There's no point me wasting bits here: check the error logging example in the INSERT documentation.

Comments:

Thanks for drawing attention to that - one of those new Oracle features that I'd failed to pay attention to. That could have been used to simplify the blog.edit_entry() procedure ( http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/fuecks_sps.html ). That actually does alot to simplify HTTP POST handling, which is often used logically equivalent manner.

Posted by Harry Fuecks on July 13, 2006 at 01:17 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today