About DML statements

You can use DML statements to manipulate data of objects in a schema, for example, to add, modify, and delete data.

Introduction

As the most frequently used SQL statements, DML statements can be used to modify data.

The following DML statements are available:

• INSERT statement: inserts a row or query results into a table or view.

• UPDATE statement: updates column values in rows of a table or view.

• MERGE statement: determines whether to use the INSERT statement or UPDATE statement based on conditions.

• DELETE statement: deletes rows from a table or view.

Examples

Execute the following statement to create a table:

CREATE TABLE customer (cust_id int primary key,cust_name varchar(8),note varchar(512));

Execute the following DML statements to modify the table:

• Execute the INSERT INTO statement to insert a row into the customer table.

  INSERT INTO customer VALUES(11,'Tom','Jacy');
  

• Execute the UPDATE statement to update a row in the customer table.

  UPDATE customer SET cust_name = 'Tomy' WHERE cust_id = 11;
  

• Run the MERGE command to insert data of the customer table into the t_merge table.

  CREATE TABLE t_merge (cust_id int primary key,cust_name varchar(8),note varchar(512));
  
  MERGE INTO t_merge t2
    USING (SELECT cust_id,cust_name,note FROM customer) t1
    ON (t2.cust_id=t1.cust_id)
    WHEN MATCHED THEN
       UPDATE SET t2.cust_name=t1.cust_name, t2.note=t1.note
    WHEN NOT MATCHED THEN
       INSERT (t2.cust_id,t2.cust_name,t2.note) values(t1.cust_id, t1.cust_name, t1.note);
  

• Execute the DELETE statement to delete the data that meets the specified condition.

  DELETE FROM customer WHERE cust_id = 11;
  

Relationship between DML statements and transactions

A transaction is a set of DML statements that make up a logical work unit. The following example helps you better understand what a transaction is: If money is transferred from Account A to Account B, three operations are involved. First, the balance in Account A decreases. Second, the balance in Account B increases. Third, the transfer is recorded in the transfer history tables of the accounts. These three operations can be collectively called a transaction.

A change made by a DML statement is persisted only when the transaction is committed. A transaction is a set of SQL statements that OceanBase Database treats as a unit. Either all of the statements are executed, or none of them are. A DML statement can also be a transaction.