CHANGING DATA (DML)

 INSERT INTO <table>[(<column_list>)]     {VALUES(<expression_list>)|<subquery>}; 

• Conditional multiple table INSERT:

   INSERT WHEN <condition> THEN INTO <table1>     WHEN <condition> THEN INTO <table2>     ...     WHEN <condition> THEN INTO <tablen> SELECT ... FROM ...; 

  
  

• Non-conditional multiple table INSERT:

   INSERT ALL INTO <table1>(<column_list>) VALUES(<expression_list>)     INTO <table2>(<column_list>) VALUES(<expression_list>)     ...     INTO <tablen>(<column_list>) VALUES(<expression_list>) SELECT ... FROM ...; 

  
  

• UPDATE:

   UPDATE <table> SET <column>=<expression>|(<subquery>) [,...] [WHERE]; 

  
  

• DELETE:

   DELETE FROM <table> [WHERE]; 

  
  

• MERGE:

   MERGE INTO <target_table> t USING <source_table> s     ON(s.<column>=t.<column> [AND|OR ...] WHEN MATCHED THEN UPDATE SET t.<column>=s.<column> ... WHEN NOT MATCHED THEN INSERT VALUES(<column list> ...); 

  
  

  • TRansactions are controlled by COMMIT (permanently store pending changes), ROLLBACK (undo pending changes), and SAVEPOINT (partial rollback to a SAVEPOINT label). The LOCK TABLE command can be used to temporarily lock tables. The SET TRANSACTION command can be used to control transaction behavior in general:

     SET TRANSACTION {READ ONLY|READ WRITE     |ISOLATION LEVEL{SERIALIZABLE|READ COMMITTED}     |USE ROLLBACK SEGMENT} [NAME '<transaction>'];