Netezza Delete Join Syntax – Deleting Referring other Table

Every application requires the data, data storage is very much important. In the digital world, we use databases (in-fact tables) to store information. Data is collected over the time and it may or may not be accurate. In some cases, you may want to delete data from the table based on the data available in other table over same or other database on same Netezza server. You cannot access the table stored on other Netezza server.

In this article, we have explained the Netezza delete join syntax and example on how to delete data from the current table using Data from other Table.

Read:

• How Netezza Update Records in Table?
• Netezza Update Join
• Netezza nzsql Command and its Usage

The process is not much different from other databases like Oracle, SQL Server, DB2, Redshift etc. Below is the Netezza delete join syntax:

Netezza Delete Join Syntax

You can use the rowed to perform deletion based on the data from other table.

delete from tableA
where rowid in (select A.rowid
from tableA A 
join tableB B
on A.col1 = B.col1);

Netezza Delete Join Example

Below is the example to delete data from the table test1 based on the data available in table test2:

create table test1 (col1 int, col2 varchar(20)) distribute on random ;
insert into test1 values(1,'abc');
insert into test1 values(2,'bcd');
insert into test1 values(3,'cde');
insert into test1 values(4,'def');

select * from test1;

COL1	COL2
1	abc
2	bcd
3	cde
4	def

create table test2 (col1 int, col2 varchar(20)) distribute on random ;
insert into test2 values(1,'pqr');
insert into test2 values(2,'xyz');
insert into test2 values(3,'yzx');

select * from test2;

COL1	COL2
1	pqr
2	xyz
3	yzx

Netezza Delete Join

delete from test1
where rowid in (select A.rowid
from test1 A 
join test2 B
on A.col1=B.col1);

select * from test1;

COL1	COL2
4	def