Amazon Redshift UPDATE Statement: 3 Comprehensive Aspects

Amazon Redshift provides a fast, reliable, and petabyte-scale Data Warehouse solution that effectively connects to Business Intelligence tools, assisting companies in gaining insights into their customers and business operations.

To generate insights, Amazon Redshift facilitates Database Developers and Data Analysts to use Structured Query Language (SQL) on the data residing in the Data Warehouse. This article gives an overview of Amazon Redshift and describes the importance of one of the most popular Data Warehouses.

It also explains the essential components of SQL commands, syntax, parameters, and usage of an Amazon Redshift UPDATE statement. Read along to learn more about using SQL in Amazon Redshift! 

Prerequisites

• An Amazon Redshift account.
• Understanding of SQL Commands.
• Basic knowledge about Redshift Data Warehouse.

Introduction to Amazon Redshift

Amazon Web Service (AWS) has provided a broad range of products and services, which includes solutions ranging from data storage to enterprise applications. As organizations require various IT services to help them scale their applications with lower IT costs, AWS became one of the most used Cloud Computing platforms in the world.

With AWS Redshift, a user can query and combine petabytes of structured and semi-structured data across a Data Warehouse, Operational Database, or Data Lake using standard SQL programming.

• Data Definition Language: Data Definition Language (DDL) deals with the structure of tables present inside the database. All the commands of DDL are auto-committed, which means they permanently save all changes in working databases. DDL commands include CREATE, ALTER, DROP, and TRUNCATE.
• Data Manipulation Language: Data Manipulation Language (DML) deals with the modification of data in databases. DML commands are not auto-committed, and hence they can be rolled back. DML commands include, INSERT, UPDATE, and DELETE.
• Data Query Language: Data Query Language (DQL) is used to fetch data from a database. It consists of a ‘SELECT’ command to choose attributes of interest. A ‘WHERE’ clause is used with DQL to return specific results from the entire data.
• Data Control Language: Data Control Language (DCL) deals with the authorization of database users. The DCL consists of ‘GRANT’ and ‘REVOKE’ commands that give database administrators authority to give various permissions by limiting access to other users.
• Transaction Control Logic: Transaction Control Logic (TCL) deals with a set of tasks as a single execution unit. The TCL commands consist of ‘COMMIT,’ ‘SAVEPOINT,’ ‘ROLLBACK,’ and ‘SET TRANSACTION.’ It is worth noting that TCL cannot be used while creating a table and can be used only with DML commands. Each transaction begins with a specific task and ends when all the relative tasks are logically executed. If any of the tasks fails, the entire transaction process fails. Thus, TCL consists of two results, success, and failure.

Understanding the Amazon Redshift UPDATE Statement

‘UPDATE’ is a DML command used to update or modify the value of a column in the table. It gives the flexibility to retrieve desired data by applying conditions using the ‘WHERE,’ ‘HAVING,’ or ‘GROUP BY’ clause. However, an end-user cannot make any modifications at the schema level in the database, thereby maintaining data privacy.

The following properties will help you while using the Amazon Redshift UPDATE command:

• Syntax of Amazon Redshift UPDATE Statement
• Parameters in Amazon Redshift UPDATE Statement

Syntax of Amazon Redshift UPDATE Statement

To work with the Amazon Redshift UPDATE statement, use the following syntax:

[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
        	UPDATE table_name [ [ AS ] alias ] SET column = { expression | DEFAULT } [,...]

[ FROM fromlist ]
[ WHERE condition ]

Parameters in Amazon Redshift UPDATE Statement

To use the Amazon Redshift UPDATE statement, you need to understand the following key parameters:

• With Clause: It is an optional clause used to specify one or more common-table-expressions.
• Alias: A temporary alternative name given to a working table is called alias. Although it is optional to consider alias for the table name, using the ‘AS’ keyword to consider alias is considered more professional.
• table_name: Any temporary or persistent table can be given an alias name. One should note that only the owner of the table or a user with ‘UPDATE’ privilege on a table can update rows. For instance, if a ‘FROM’ clause is used in an expression, a user must have ‘SELECT’ privilege on those tables.
• SET column: When a user wants to modify one or more columns, ‘SET’ is used to update such columns. The unspecified columns will save their current value.
• Expression: The expression determines the new value of the specified column. By default, the column is updated with the default value that the column is assigned when using the CREATE table operator.
• FROM tablelist: When updating a table, a user can give reference to the information present in another table. Such tables are listed either in the ‘FROM’ clause or approached using a subquery with a ‘WHERE’ condition.
• WHERE condition: A ‘WHERE’ clause is an optional clause that restricts the update command to certain matching rows.

Usage of Amazon Redshift UPDATE Statement

Amazon Redshift allows a maximum size of 16 MB for any single SQL statement. When updating a large number of lines in the table, you must restore storage and recourse values. 

A JOIN clause is used to combine rows from two or more tables based on the related columns. Following is the usage of the UPDATE statement when combined with the JOIN clause:

• Left, Right, and Full outer joins would return an error if updated using the FROM clause.
• While specifying an outer join, use a subquery in the WHERE clause of an UPDATE statement.
• If the UPDATE statement requires a self-join to a target table, a JOIN Condition must be specified with WHERE clause criteria that qualify rows for the update operation. 

What Are The Examples of UPDATE Statement?

To understand the Amazon Redshift UPDATE statement, let’s consider a sample database called TICKIT readily available in Amazon Redshift documentation. This small database has 7 tables of which 2 are fact tables and the remaining are dimensions tables. The category table in the TICKIT database contains the following rows.

Now, you will learn the following examples of updates on the above table:

• Table Update Using a Range of Values
• Table Update Using a Current Value
• Table Update Using the Result of a WHERE Clause Subquery
• Table Update Using the Result of a Join Condition
• Table Update Using the Outer Joins in the FROM Clause

1. How to update a table based on a range of values?

To update the CATGROUP column based on a range of values in the CATID column, follow the below given Amazon Redshift UPDATE query:

UPDATE category
SET catgroup='Theatre'
WHERE catid BETWEEN 6 AND 8;

SELECT * FROM category
WHERE catid BETWEEN 6 AND 8;

Output

2. How to Update a table based on a current value?

To update the CATNAME and CATDESC columns based on their current CATGROUP value, follow the below given Amazon Redshift UPDATE query:

UPDATE category
SET catdesc=default, catname='Shows'
WHERE catgroup='Theatre';

SELECT * FROM category
WHERE catname='Shows';

Output

In the above case, if the CATDESC column does not have any defined default value, it is automatically set to a null value when the table is created.

3. How to Update a Table Using the Result of a WHERE Clause Subquery?

When you update the CATEGORY table based on the result of a subquery in the WHERE clause, follow the below given Amazon Redshift UPDATE query:

UPDATE category
SET catdesc='Broadway Musical'
WHERE category.catid IN
(SELECT category.catid FROM category
JOIN event ON category.catid = event.catid
JOIN venue ON venue.venueid = event.venueid
JOIN sales ON sales.eventid = event.eventid
WHERE venuecity='New York City' AND catname='Musicals');

On successful execution of the above query, you will see the below output: 

4. How to Update a table based on the result of a WITH clause subquery?

WITH u1 as (SELECT catid FROM event ORDER BY catid DESC LIMIT 1) 
UPDATE category SET catid='200' FROM u1 WHERE u1.catid=category.catid;

SELECT * FROM category ORDER BY catid DESC LIMIT 1;

Output

Learn more about Redshift Statements by reading our blog on Redshift CASE Statements.

Usage Note for Redshift UPDATE Statement

After updating a large number of rows in a table:

• Analyze the table to update statistics for the query planner.
• Vacuum the table to reclaim storage space and re-sort rows.
• Left, right, and full outer joins aren’t supported in the FROM clause of an UPDATE statement.

Conclusion

This article introduced Amazon Redshift and discussed the various type of SQL queries. Moreover, it explained the UPDATE command that is extremely useful when working with Amazon Redshift. It also provided the examples, use and syntax for using the Amazon Redshift UPDATE statement.

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 150+ data sources to Cloud-based Data Warehouses like Amazon Redshift, Snowflake, Google BigQuery, etc. It will provide you a hassle-free experience and make your work life much easier.

Sign up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Frequently Asked Questions