-
Data compare on insert problem
I have data I am importing from one table into another table. Before I do the inserts, I want to validate certain rules (does record already exist, etc) and send that message back to the table I am inserting from. In the example below, I am inserting from table A to table B and recording failures in table A
Table A consists of 3 columns:
recordID, recordtag, modifieddate, processed
Table B consists of 3 columns:
recordID, recordtag, modifieddate
if a record from table a already exists in table b, I want to send a message of "not processed: already exists" to table A's Processed column.
There are multiple rules I want to process prior to insert. How do I do this?
thanks in advance!!
-
Double or nothing?
You may need two statements:
Code:
UPDATE tablea a
SET processed = 'Not processed: already exists'
WHERE EXISTS (SELECT '?'
FROM tableb b
WHERE b.recordid = a.recordid);
INSERT INTO tableb
SELECT a.recordid, a.recordtag, SYSDATE
FROM tablea a
WHERE NOT EXISTS (SELECT '?'
FROM tableb b
WHERE b.recordid = a.recordid);
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
How can I do this with a trigger?
-
Could you please better describe the process like...
... where are you planning to put the trigger?
... what is going to trigger the trigger?
I think you are pretty close of finding yourself facing mutating table syndrome, just a guess.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|