| 1 |
----------------- |
| 2 |
UPDATE OR INSERT statement |
| 3 |
----------------- |
| 4 |
|
| 5 |
Function: |
| 6 |
Allow to update or insert a record based on the existence (checked with IS NOT DISTINCT) or not of it. |
| 7 |
|
| 8 |
Author: |
| 9 |
Adriano dos Santos Fernandes <[email protected]> |
| 10 |
|
| 11 |
Syntax rules: |
| 12 |
UPDATE OR INSERT INTO <table or view> [(<column_list>)] |
| 13 |
VALUES (<value_list>) |
| 14 |
[MATCHING (<column_list>)] |
| 15 |
[RETURNING <value_list> [INTO <variable_list>]] |
| 16 |
|
| 17 |
Scope: |
| 18 |
DSQL, PSQL |
| 19 |
|
| 20 |
Examples: |
| 21 |
1. UPDATE OR INSERT INTO T1 (F1, F2) VALUES (:F1, :F2); |
| 22 |
2. UPDATE OR INSERT INTO EMPLOYEE (ID, NAME) VALUES (:ID, :NAME) RETURNING ID; |
| 23 |
3. UPDATE OR INSERT INTO T1 (F1, F2) VALUES (:F1, :F2) MATCHING (F1); |
| 24 |
4. UPDATE OR INSERT INTO EMPLOYEE (ID, NAME) VALUES (:ID, :NAME) RETURNING OLD.NAME; |
| 25 |
|
| 26 |
Notes: |
| 27 |
1. When MATCHING is omitted, the existence of a primary key is required. |
| 28 |
2. INSERT and UPDATE permissions are needed on <table or view>. |
| 29 |
3. If the RETURNING clause is present, then the statement is described as |
| 30 |
isc_info_sql_stmt_exec_procedure by the API. Otherwise it is described |
| 31 |
as isc_info_sql_stmt_insert. |
| 32 |
|
| 33 |
Limitation: |
| 34 |
1. A singleton error will be raised if the RETURNING clause is present and more than |
| 35 |
one record match the condition. |
| 36 |
2. There is no "UPDATE OR INSERT ... SELECT ..." as "INSERT ... SELECT". Use MERGE for |
| 37 |
this type of functionality. |