Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    4

    Question Unanswered: Update changes between two tables In MySQL

    Everyday I get a refreshed table with updates and new records added to the table. What I am trying to do is compare the data between the two tables and if there are any rows that have been modified in table 2 I need to make the necassary changes in table 1, and if there are any new rows added I need to add those rows. Here is a more graphic explanation, maybe it will help clarify my dilemma:

    Table 1

    1000 | 100 | 10
    2000 | 200 | 20

    Table 2
    1000 | 100 | 10
    2000 | 200 | 10

    Notice table two row 2 has 2000 | 200 | "10" and in table one it is 2000 |
    200 |"20". I need a script that would update table 1 to make replace 20 with 10 automatically once I run the update query daily.

    If anyone has any suggestions or knows how to write the sql code for these updates that would be great.

    Thanks,

    MSP

  2. #2
    Join Date
    Jun 2003
    Location
    Binghamton, NY
    Posts
    8
    Will field 1 and 2 stay static? That is to say - what is your key field? If you have one you could use the REPLACE INTO table command.

  3. #3
    Join Date
    Jun 2003
    Posts
    4

    Question

    Yes, i'm sorry I didn't make that clear, field 1 is the primary key, field 2 and 3 are normal varchar attributes. So basically all I need to do is search for any changes made to the fields and update them according to their Field 1 values.

  4. #4
    Join Date
    Jun 2003
    Location
    Binghamton, NY
    Posts
    8
    REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted.
    That is taken from section 6.4.8 of the MySQL manual. So since you have a PK on the table in field1, you should be able to use this.

    REPLACE INTO table2 SELECT * FROM table1;

  5. #5
    Join Date
    Jun 2003
    Posts
    4
    thanks.

Posting Permissions

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