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.

• 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));

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;
  

• Execute the DELETE statement to delete the data that meets 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.