Amazon Redshift Delete with Join Syntax and Examples

Post author:Vithal S
Post last modified:July 6, 2022
Post category:Redshift
Reading time:4 mins read

Data is an integral part of decision making system. Many application reply on a data to make business decisions. In the relational database world, data is stored in the form of tables. Today, we have unlimited storage, but cost is also high to manage storage. Data is collected over the time and it may or may not be accurate. You may have to clean the data by deleting unwanted records or purge the old data from a system that is outdated. In this article, we will discuss Amazon Redshift delete statement with join, its syntax and some examples.

Redshift Delete Join

The process of deleting table data using other table is similar to that available in Netezza, Oracle, Snowflake, etc. But, keep in mind that, Redshift does not allow cross database access. That is, you cannot access table stored in other database in the same Redshift cluster. However, you can access tables from another schema.

Redshift Delete Join Syntax

You can use the following syntax to delete data using another table.

DELETE [ FROM ] table_name
    [ {USING } table_name2, ... ]
    [ WHERE condition ]

Where,

FROM clause is optional when you are not using any other table.
table_name: A temporary or persistent table.
USING table_name: The USING keyword is used when one or more additional tables are referenced in the WHERE clause condition

Redshift Delete Join Example

Following example demonstrates the Amazon Redshift delete statement using another table.

delete from event 
using sales 
where event.eventid=sales.eventid;

In the above example, delete statement deletes all of the rows from the EVENT table that satisfy the join condition over the EVENT and SALES tables. The SALES table must be explicitly named in the FROM list.

EXISTS Clause as a Redshift Delete Join Alternative

It is recommended to use EXISTS when you join tables on multiple columns. You need to correlate the tables to evaluate joining condition

delete from event 
where exists 
(select 1 from sales 
where event.eventid=sales.eventid
and event.stateid=sales.sateid);