Firebird Database statement: updateorinsert

Updateorinsert is a statement added in Firebird2.1. In this statement, you can determine whether a specific record exists in the target table based on the matching field. If no matching record exists, execute Insert, otherwise, other fields except matching fields are updated. The matched field must be the primary key. If you are using a trigger or stored procedure, you can also use the Re

Update or insert is a statement added in Firebird2.1. In this statement, you can determine whether a specific record exists in the target table based on the matching field. If no matching record exists, execute Insert, otherwise, other fields except matching fields are updated. The matched field must be the primary key. If you are using a trigger or stored procedure, you can also use the Re

Update or insert is a statement added in Firebird2.1. In this statement, you can determine whether a specific record exists in the target table based on the matching field. If no matching record exists, execute Insert, otherwise, other fields except matching fields are updated. The matched field must be the primary key. If it is used in a trigger or stored procedure, you can use the Returning clause to return a specific value to the variable.

This document describes the following:

UPDATE OR INSERT INTO   {tablename | viewname} [(
 
  )]   VALUES (
  
   )   [MATCHING (
   
    )]   [RETURNING 
    
      [INTO 
     
      ]]
      
        ::= colname [, colname ...]
       
         ::= value [, value ...]
        
          ::= :varname [, :varname ...]
        
       
      
     
    
   
  
 

A simple example:

Update or insert into khinfo (kh_code, kh_name) values ('000000', 'kunming XXX Co., Ltd. ') matching (kh_code)

Note: The Update or insert statement is used to determine whether to add a new record to the khinfo table. The matching condition is the primary key field kh_code. The execution result is processed based on whether the KH_Code exists or not.

This statement combines the Insert or Update statements that previously created two conditions into one statement to determine whether the primary key exists, the Firebird Database completes the judgment and execution of insert or Update operations, which is very concise.