Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003

    Unanswered: insert record problem


    Here's my problem.

    I have a table called MAIN with MID(2-digit number) and COD(5-digit number) as the primary key(combined). I have another table called HISTORY which monitors changes to records of MAIN. It also uses the MID and COD, plus a field called NUM which combined with the MID and COD creates a Primary key. HISTORY and MAIN have a one to many relationship.

    - MID - Primary
    - COD - Key

    - MID - Primary
    - COD -
    - NUM - Keys
    - EVENT- should be 1 initially
    - WHEN - Date the record was added.

    I regularly receive a file that contains the old and new records. They get imported to MailDBImport which in turn I ue to update MAIN and HISTORY tables. The new records need to be added to MAIN. How should my query look like if I only want to import the new records?

    Also, I need to create a record in the HISTORY table for each of the new MAIN records added. How can I do an INSERT statement to create a record in HISTORY that would copy the MID and COD from MAIN and also add my own values to the record? I tried this statement and it gives me an error.

    INSERT INTO History ( MID, COD_IIN, 1,1,#06/30/2004#,1,TRUE) SELECT MID, COD_IIN FROM MailDBImport WHERE (((MailDBImport.MID) Not In (SELECT Main.MID FROM Main INNER JOIN History ON (Main.COD_IIN = History.COD_IIN) AND (Main.MID = History.MID))) AND (MailDBImport.COD_IIN) Not In (SELECT Main.COD_IIN FROM Main INNER JOIN History ON (Main.COD_IIN = History.COD_IIN) AND (Main.MID = History.MID))))

    Once this statement gets fixed, how could I make the date(06/30/2004) reflect today's date instead of having to manually edit the query?

    Hope someone here can help me.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    insert into tableA values (1, date())
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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