Hi everyone. I just joined the community and I need some advice. I am a college student studying computer science going into my sophomore year, so my understanding of SQL is mediocre at best. I am working with apex, I have a tabular form where the user can add records. I created a button and added dynamic action, where upon clicking the button, I have apex execute pl/sql code:
INSERT INTO table_B (value_ID, value_A, value_B, value_C)
SELECT sequence.NEXTVAL, value_A, value_B, value_C
FROM table_A
WHERE condition LIKE '%so and so%';
After the code, table_B now has value_ID. I need to take that value_ID and insert it back into table_A for reference purposes.
I was using (and I know its wrong) the following code
UPDATE table_A
SET value_ID = (SELECT MAX(value_id)
FROM table_B)
WHERE condition LIKE ‘%so and so%’;
This pulls the maximum value each and every time and re-updates all records. So if I inserted one record (value_ID 100), and then inserted three more records, instead of having 100,101,102,103, all value_ID’s are displayed as 103. I need to document inserted ID for each record so it corresponds with the ID in table_B
Is there a way I can do this?
Thanks