Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Insert if not present

    Hi,

    I am writing a program which is supposed to do following thing

    There are 2 tables TABLE_REPORT and TABLE_MESSAGE

    Scan database after regular interval and see if TABLE_REPORT.ReportStatus = NEW AND TABLE_MESSAGE.Status = NEW where TABLE_REPORT.id = TABLE_MESSAGE.id

    Process the record and set both statuses to PROCESSED.

    catch is it is not necessary to have entry in TABLE_MESSAGE for that id if it is there mark it as processed but if not I am suposed to insert one entry with status Processed.

    Can anybody give me some guideline to do this in effective way.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    expected result is to mark record in parent table as Processed and mark record in child table as processed if presnt and insert if not

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use MERGE command
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    Just to give more info
    1) Check TABLE_REPORT.ReportStatus = NEW AND TABLE_MESSAGE.STATUS = NEW OR NULL. Compile data do business processing
    2) Insert TABLE_MESSAGE.STATUS = NEW if record not present in TABLE_MESSAGE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •