In the red shift I have written the query like this
BEGIN;
INSERT INTO qa_rims_data.public.Master
SELECT *
FROM qa_rims_data.public.Staging
WHERE tag_number NOT IN (SELECT tag_number FROM qa_rims_data.public.Master);
– Update existing records in qa_rims_data.public.Master from qa_rims_data.public.Staging
UPDATE qa_rims_data.public.Master m
SET
tag_number = s.tag_number,
“date” = s.“date”,
quantity = s.quantity,
Disposition = s.Disposition,
Grade = s.Grade,
“Return Reason” = s.“Return Reason”,
Unitprice = s.Unitprice,
Recovered = s.Recovered,
Status = s.Status,
“Process Status” = s.“Process Status”,
Processed = s.Processed,
Generated = s.Generated,
Channel = s.Channel,
Brand = s.Brand,
own_label = s.own_label,
“Site Name” = s.“Site Name”,
Category_l1 = s.Category_l1,
Category_l2 = s.Category_l2,
Category_l3 = s.Category_l3,
“Source Code” = s.“Source Code”,
latitude = s.latitude,
longitude = s.longitude,
State = s.State,
City = s.City,
dispatch_date = s.dispatch_date,
inward_user_id = s.inward_user_id,
“Destination Code” = s.“Destination Code”,
grader_name = s.grader_name,
inwarding_disposition = s.inwarding_disposition,
“Actual Tat” = s.“Actual Tat”,
“Tat Status” = s.“Tat Status”,
“Expected Tat” = s.“Expected Tat”,
“Return Type” = s.“Return Type”
FROM qa_rims_data.public.Staging s
WHERE m.tag_number = s.tag_number ;
COMMIT;
like these same query i have written another way like this
BEGIN;
– Delete records from qa_rims_data.public.Master where tag_number matches
DELETE FROM qa_rims_data.public.Master
WHERE tag_number IN (SELECT tag_number FROM qa_rims_data.public.Staging);
– Insert all records from qa_rims_data.public.Staging into qa_rims_data.public.Master
INSERT INTO qa_rims_data.public.Master
SELECT *
FROM qa_rims_data.public.Staging;
COMMIT;
i think it will be work for my case