I have three tables as follows:
Table_A:
id Desc Qty
1001 xx"23zp 4
1002 zpll"340 2
1003 sod"34LN 5
Table_B:
id Desc Qty
1001 xx"23zp 2
1002 zpll"340 5
1003 sod"34LN 7
Table_C:
id Desc Qty
1002 xx"23zp 2
1005 aqr"340 6
1008 sod"34LN 5
Table_C is the external table located in different schema. Table_A and Table_B are in same schema.
Objective:
If Table_C.id = Table_A.id then INSERT the record INTO Table_B. If the same id is present in Table_B then UPDATE the same.
If Table_C.id <> Table_A.id then INSERT the record INTO Table_A.
In order to achieve the above, I have developed a function with id as input parameter and within that function I am using the below code snippet.
CREATE temp TABLE new_records_indtls ON COMMIT DROP AS
SELECT c.id, c.Desc, c.Qty, a.id AS 'Tbl_A_id'
FROM Table_C c
LEFT OUTER JOIN Table_A
ON c.id = a.id
-- Inserting record into Table_B if the id is present in Table_A
INSERT INTO Table_B (id,"Desc","Qty")
SELECT id,Desc,Qty FROM new_records_indtls
WHERE "Tbl_A_id" NOTNULL
ON CONFLICT(id) DO UPDATE
"Desc" = EXCLUDED."Desc",
"Qty" = EXCLUDED."Qty";
-- Inserting records into Table_A if the id is not present in Table_A
INSERT INTO Table_A (id,'Desc','Qty')
SELECT id,"Desc","Qty" FROM new_records_indtls
WHERE "Tbl_A_id" IS NULL;
So using the above code, we should see the final Table_A and Table_B as below:
Table_A:
id Desc Qty
1001 xx"23zp 4
1002 zpll"340 2
1003 sod"34LN 5
1005 aqr"340 6
1008 sod"34LN 5
Table_B:
id Desc Qty
1001 xx"23zp 2
1002 zpll"340 2 <---Note that the Qty has been UPDATEd
1003 sod"34LN 7
But I am not able to see the records pertaining to 1005 and 1008 in Table_A. However, in Table_B records were seen updated properly.
What I am missing here? I have also tried another approach using data-modifying CTEs in this SO thread, but no luck. Can anybody provide a clue?
