Updated on 2025-09-18 GMT+08:00

Updating a Table by Using DML Statements

In DWS, you can update a table by running DML statements.

Procedure

Assume that there is a table customer_t and the table structure is as follows:

1
2
3
4
5
6
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
      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
      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 about how to use INSERT, see "TRUNCATE" in the SQL Syntax.

  • Run the UPDATE statement to update data in the table. Change the value of the c_customer_id column to 0.
    1
    UPDATE customer_t SET c_customer_id = 0;
    

    For details about how to use UPDATE, see "UPDATE" in the SQL Syntax.

  • Run the DELETE statement to delete rows from the table.
    You can use the WHERE clause to specify the rows whose data is to be deleted. If you do not specify it, all rows in the table are deleted and only the data structure is retained.
    1
    DELETE FROM customer_t WHERE c_last_name = 'Baker';
    

    For details about how to use DELETE, see "DELETE" in the SQL Syntax.

  • Run the TRUNCATE statement to delete all rows from the table.
    1
    TRUNCATE TABLE customer_t;
    

    For details about how to use TRUNCATE, see "TRUNCATE" in the SQL Syntax.

    When deleting data from a table, the DELETE statement deletes a row of data each time while 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.