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.
- Insert a row to the customer_t table.
- 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.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.