DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Data compare on insert problem

  1. #1
    Join Date
    Sep 2009
    Location
    Tacoma, Wa
    Posts
    2

    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!!

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool 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

  3. #3
    Join Date
    Sep 2009
    Location
    Tacoma, Wa
    Posts
    2
    How can I do this with a trigger?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
  •  


Click Here to Expand Forum to Full Width