About DML statements

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

Introduction

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

You can use DML statements for the following purposes:

• 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.

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

• REPLACE INTO statement: inserts or replaces data in a table.

Examples

Execute the following statement to create a table:

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

Examples for some DML statements:

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

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

• Use UPDATE to update a row in the customer table.

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

• Use DELETE to delete the data meeting the specified condition.

  DELETE FROM customer WHERE cust_id = 11;
  

• Use REPLACE INTO to insert data.

  REPLACE INTO customer VALUES(11,'Tom','Lucy');
  

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.