In a data warehouse, application may have various data sources. All these data sources are stored in tables in the database. Data is collected over the time and it may or may not be accurate. In some cases, you may want to update the table values based on the data available in another table over same or other database on the same server. For example, in a case of slowly changing dimension type 2, you may want to update the dimension table using a stage table that we created to hold received data. In this article, we will check Vertica update join to update Vertica tables using another table data.
Vertica Update Join
Update table is one of the best option when you want to implement SCD type 2 to track historic records. SCD type 2 updates target table by using source table data.
The update process in Vertica is not much different from other databases like Netezza, Oracle, SQL Server, DB2 etc.
Related Articles:
Vertica Update Join Syntax
Syntax is similar to other database update join.
Below is the update join syntax:
UPDATE Table_A as A
SET col2 = B.col2
FROM Table_B as B
WHERE A.col1 = B.col1; Vertica Update Join Examples
Before going into examples let us create sample tables with test data.
Create Table_A and insert sample records:
create table table_A (col1 int, col2 varchar(20));
insert into table_A values(1,'abc');
insert into table_A values(2,'bcd');
insert into table_A values(3,'cde'); Create Table_B and insert sample records:
create table table_B (col1 int, col2 varchar(20));
insert into table_B values(1,'pqr');
insert into table_B values(2,'xyz');
insert into table_B values(3,'yzx'); Below is the example to updates the table table_A based on the data available in table table_B:
UPDATE table_A as A
SET col2 = B.col2
FROM table_B as B
WHERE A.col1 = B.col1;Now verify the table_A for updated records.
Related Articles,
Hope this helps 🙂