Hi everyone,
I am not a developer and my SQL knowledge is very limited, however I'm trying something new in APEX and was wondering if I could get some help with this:
There are 2 schemas and I need one to extract data from the other schema to write to a couple columns. However, it gets a little complicated (at least in my mind):
Schema 1
Schema 2
Table in Schema 1: TABLE_1
Table in Schema 2: TABLE_2
Schema 1 has access to Schema 2
In TABLE_2, there are these columns: COLUMN_21, COLUMN_22, and COLUMN_23
In TABLE_1, there are these columns: COLUMN_11, COLUMN_12, and COLUMN_13
Consider COLUMN_23 and COLUMN_13 as the unique identifiers.
In the TABLE_2, the SUM of all values in COLUMN_21 needs to be calculated where COLUMN_23 equals to the value in COLUMN_13 (from the TABLE_1). The same for COLUMN_22 as well. The two SUMs will then need to be inserted into COLUMN_11 and COLUMN_12 respectively in TABLE_1 where again the COLUMN_13 value needs to equal to COLUMN_23 (TABLE_2).
Currently, I have this:
INSERT INTO SCHEMA1.TABLE_1 (COLUMN_11)
SELECT SUM(COLUMN_21) from SCHEMA2.TABLE_2;
This calculates the SUM and creates a new row in TABLE_1 rather than identifying the unique identifier for the particular row and inserting the values into the columns for that row. Not sure where and how I can add a WHERE clause because of the different schemas (would it be SCHEMA1.TABLE_1.COLUMN_11? cause that didn't work). I also tried adding a UNION to have both queries, however I run into missing keywords and parenthesis errors.
If anyone could shed some light for me that would be extremely helpful and I appreciate any assistance. Thank you!