Updating a Table by Using DML Statements

Procedure

There is a table named customer_t and the table structure is as follows:

1
2
3
4
5
6

openGauss=# CREATE TABLE customer_t
( c_customer_sk             integer,   
  c_customer_id             char(5),    
  c_first_name              char(6),    
  c_last_name               char(8) 
) ;

You can run the following DML statements to update data in the table.

• Run the INSERT statement to insert data into the table.
  • Insert a row to the customer_t table.

    1	openGauss=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (3769, 5, 'Grace','White');

  • Insert multiple rows to the customer_t table.

    1 2 3 4 5

    openGauss=# INSERT INTO customer_t (c_customer_sk, c_customer_id, c_first_name,c_last_name) VALUES (6885, 1, 'Joes', 'Hunter'), (4321, 2, 'Lily','Carter'), (9527, 3, 'James', 'Cook'), (9500, 4, 'Lucy', 'Baker');

    For details on how to use INSERT, see Inserting Data to a Table.

• Run the UPDATE statement to update data in the table. Change the value of the c_customer_id column to 0.

  1	openGauss=# UPDATE customer_t SET c_customer_id = 0;

  For details on how to use UPDATE, see UPDATE.

• Run the DELETE statement to delete rows from the table.
  You can use the WHERE clause to specify the rows whose data is to delete. If you do not specify it, all rows in the table are deleted and only the data structure is retained.

  1	openGauss=# DELETE FROM customer_t WHERE c_last_name = 'Baker';

  For details on how to use DELETE, see DELETE.

• Run the TRUNCATE statement to delete all rows from the table.

  1	openGauss=# TRUNCATE TABLE customer_t;

  For details on how to use TRUNCATE, see TRUNCATE.

  The DELETE statement deletes a row of data each time whereas the TRUNCATE statement deletes data by releasing the data page stored in the table. Therefore, data can be deleted more quickly by using TRUNCATE than using DELETE.

  DELETE deletes table data but does not release table storage space. TRUNCATE deletes table data and releases table storage space.