I have taken 2 table "temp_add_1" and "temp_add_2".
Table temp_add_1 have 11 records and temp_add_2 have 13 records ( new records and updated columns)
Records of table temp_add_1 with old records
5000000196 KAPLAN UNIVERSITY 332 FRONT ST S STE 501 LA CROSSE WI 54601
5000000206 GLATFELTER 475 S PAINT ST STOREROOM RECEIVING CHILLICOTHE OH 45601
5000000209 METHODIST HEALTH SYSTEM 1441 N BECKLEY AVE DALLAS TX 75203
5000000220 WINDWOOD FARM 4857 WINDWOOD FARM RD AWENDAW SC 294295951
5000239439 D & H DISTRIBUTING 185 COWETA INDUSTRIAL PAR NEWNAN GA 30265 US
Table temp_add_1 have 11 records and temp_add_2 have 13 records ( new records and updated columns)
Records of table temp_add_1 with old records
5000000196 KAPLAN UNIVERSITY 332 FRONT ST S STE 501 LA CROSSE WI 54601
5000000206 GLATFELTER 475 S PAINT ST STOREROOM RECEIVING CHILLICOTHE OH 45601
5000000209 METHODIST HEALTH SYSTEM 1441 N BECKLEY AVE DALLAS TX 75203
5000000220 WINDWOOD FARM 4857 WINDWOOD FARM RD AWENDAW SC 294295951
5000239439 D & H DISTRIBUTING 185 COWETA INDUSTRIAL PAR NEWNAN GA 30265 US
Records of table temp_add_2 with new rows.
5000000209 METHODIST HEALTH SYSTEM 1441 N BECKLEY AVE DALLAS TX 75203
5000000220 WINDWOOD FARM 4857 WINDWOOD FARM RD AWENDAW SC 294295951
5000000281 MARCO ST CLOUD OFFICE 4510 HEATHERWOOD RD ST CLOUD MN 56301
5000000294 RJ REYNOLDS TOBACCO 1001 REYNOLDS BLVD BLDG 603-5 DOCK 21 WINSTON SALEM NC 27105
5000239439 D & H DISTRIBUTING 185 COWETA INDUSTRIAL PARKWAY NEWNAN GA 30265 US
Use below command to update the records in temp_add_1.
I am using CASE clause with FULL OUTER JOIN
insert overwrite table sales_channel.temp_add_1
select
case
when t1.address_identifier = t2.address_identifier then t1.address_identifier else t2.address_identifier end as address_identifier,
case
when t1.customer_name = t2.customer_name then t1.customer_name else t2.customer_name end as customer_name,
case
when t1.address1_description = t2.address1_description then t1.address1_description else t2.address1_description end as address1_description,
case
when t1.address2_description = t2.address2_description then t1.address2_description else t2.address1_description end as address2_description,
case
when t1.city_name = t2.city_name then t1.city_name else t2.city_name end as city_name,
case
when t1.state_name = t2.state_name then t1.state_name else t2.state_name end as state_name,
case
when t1.zip_code = t2.zip_code then t1.zip_code else t2.zip_code end as zip_code,
case
when t1.iso_country_code = t2.iso_country_code then t1.iso_country_code else t2.iso_country_code end as iso_country_code
from sales_channel.temp_add_1 t1 FULL OUTER JOIN sales_channel.temp_add_2 t2
ON t1.address_identifier = t2.address_identifier;
Result:-
OK
5000000209 METHODIST HEALTH SYSTEM 1441 N BECKLEY AVE DALLAS TX 75203
5000000220 WINDWOOD FARM 4857 WINDWOOD FARM RD AWENDAW SC 294295951
5000000281 MARCO ST CLOUD OFFICE 4510 HEATHERWOOD RD 4510 HEATHERWOOD RD ST CLOUD MN 56301
5000000294 RJ REYNOLDS TOBACCO 1001 REYNOLDS BLVD 1001 REYNOLDS BLVD WINSTON SALEM NC 27105
5000239439 D & H DISTRIBUTING 185 COWETA INDUSTRIAL PARKWAY NEWNAN GA 30265 US
