Netezza Update Join Syntax – Update using other Table

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

Every application that require 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 update the table based on the data available in other table over same or other database on same server. In this article, we have explained the Netezza Update Join Syntax and example on how to update using Data in other Table.

Read:

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

Netezza Update Join Syntax

UPDATE Table A
SET col2 = B.col2
FROM Table B
WHERE
A.col1 = B.col1; --Joining condition

Netezza Update Join Example

Below is the example to updates 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');

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 update Join

UPDATE test1 A
SET col2 = B.col2
FROM test2 B
WHERE
A.col1 = B.col1;

select * from test1;
COL1 COL2
1 pqr
2 xyz
3 yzx
4 def

Read:

Tags: , ,