----------------- UPDATE OR INSERT statement ----------------- Function: Allow to update or insert a record based on the existence (checked with IS NOT DISTINCT) or not of it. Author: Adriano dos Santos Fernandes Syntax rules: UPDATE OR INSERT INTO [()] VALUES () [MATCHING ()] [] [] [] [RETURNING [INTO ]] Scope: DSQL, PSQL Examples: 1. UPDATE OR INSERT INTO T1 (F1, F2) VALUES (:F1, :F2); 2. UPDATE OR INSERT INTO EMPLOYEE (ID, NAME) VALUES (:ID, :NAME) RETURNING ID; 3. UPDATE OR INSERT INTO T1 (F1, F2) VALUES (:F1, :F2) MATCHING (F1); 4. UPDATE OR INSERT INTO EMPLOYEE (ID, NAME) VALUES (:ID, :NAME) RETURNING OLD.NAME; Notes: 1. When MATCHING is omitted, the existence of a primary key is required. 2. INSERT and UPDATE permissions are needed on
. 3. If the RETURNING clause is present, then the statement is described as isc_info_sql_stmt_select by the API. Otherwise it is described as isc_info_sql_stmt_insert. Limitation: 1. There is no "UPDATE OR INSERT ... SELECT ..." as "INSERT ... SELECT". Use MERGE for this type of functionality.