Hallo Oracle Community,
Setup: Oracle 12.2 Database, two tables called A (original set of data) and B (destiny). I want to load some records from table A into table B. So I made an INSERT INTO SELECT statement, no magic so far. Inside of the SELECT statement i habe a virtual column called error_message, which I fill with text messages for example when a value is not a correct Date. In there WHERE clause I filter the records with error_message IS NULL, so I dont try to insert bad records, still no magic it works fine.
The problem is, I want to catch the records with an error message and update table A (set the status value and fill the error message). Normaly I would use a MERGE statement, but since I want to INSERT into table B and UPDATE A, MERGE statement is not working. What I am doing at the moment is, I fire the same query (difference only for the error_message column in the WHERE clause with IS NULL or IS NOT NULL) two times, one time for the INSERT, the other time for the UPDATE. My question is, is there another smart way with plain SQL (no pl/sql, no temp tables) to avoid the two statements ?
DataVader