Professional Web Applications Themes

Update only changed records between two tables - MySQL

I am duplicating a database of student information (I don't want to mess with the Student Information System's database). What I would like to do is to be able to identify only the changed records, on a daily basis, between the extract of the SIS database and my copy. I have worked out a method for extracting the SIS database and loading it into an mysql database, but now I am stuck. I have no clue how to go about the following: 1. Update a "changed_records" database on a daily basis with just the diff from the previous day's extract ...

  1. #1

    Default Update only changed records between two tables

    I am duplicating a database of student information (I don't want to
    mess with the Student Information System's database). What I would
    like to do is to be able to identify only the changed records, on a
    daily basis, between the extract of the SIS database and my copy.

    I have worked out a method for extracting the SIS database and loading
    it into an mysql database, but now I am stuck. I have no clue how to
    go about the following:

    1. Update a "changed_records" database on a daily basis with just the
    diff from the previous day's extract (to include new and deleted
    records)
    2. Update the copied database with the changes (including timestamps
    for modification date_time and created date_time)

    I hope this was descriptive enough!

    Thanks,
    Shane

    Shane Guest

  2. #2

    Default Re: Update only changed records between two tables

    Shane wrote: 

    to answer your question i have to ask two questions from you:
    1-what platform is the actual sis on
    2-what platform is your mysql server on
    lark Guest

  3. #3

    Default Re: Update only changed records between two tables

    On Apr 26, 6:46 am, lark <net> wrote: 




    >
    > to answer your question i have to ask two questions from you:
    > 1-what platform is the actual sis on
    > 2-what platform is your mysql server on[/ref]

    They are both currently on Win32 platforms. The SIS will always be on
    a Win32 platform, the duplicate data may be moving to Linux since
    there really is no need for it to be on a Win32 machine.

    Shane Guest

  4. #4

    Default Re: Update only changed records between two tables

    On Apr 26, 6:46 am, lark <net> wrote: 




    >
    > to answer your question i have to ask two questions from you:
    > 1-what platform is the actual sis on
    > 2-what platform is your mysql server on[/ref]

    1. Win32 - must be Win32
    2. Win32 - Can be Linux

    Shane Guest

  5. #5

    Default Re: Update only changed records between two tables

    Shane wrote: 
    >> to answer your question i have to ask two questions from you:
    >> 1-what platform is the actual sis on
    >> 2-what platform is your mysql server on[/ref]
    >
    > They are both currently on Win32 platforms. The SIS will always be on
    > a Win32 platform, the duplicate data may be moving to Linux since
    > there really is no need for it to be on a Win32 machine.
    >[/ref]

    you can use a product called sqlyog to schedule midnight extracts from
    the sis database to the mysql database. all's you need is the product
    (it's rather cheap to purchase) and two DSN's defined on the machine
    that runs sqlyog each pointing to the 2 databases with sufficient
    privileges for the users defined in DSN. you then connect to the two
    databases within sqlyog and synchronize the databases and/or tables in
    databases based on whatever criteria that you have. this product can
    synchronize a mysql table with any data source.

    i have some jobs that kick in at different hours of the night one after
    the other. the job determines if it needs to update a row in my
    destination database and if so, it will otherwise, it won't bother and
    on to the next row. btw, you can do all of these with perl scripts
    scheduled that are tied to cron jobs but you'll have to write all of
    them yourself. it's cheaper to buy sqlyog in my opinion.

    hope this helps.
    lark Guest

Similar Threads

  1. SQL, counting records, two tables, the zero factor...
    By Thomas in forum Coldfusion - Getting Started
    Replies: 20
    Last Post: January 13th, 09:09 AM
  2. update multiple records in multiple tables from one form
    By Anj01 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 17th, 05:54 AM
  3. Adding records to tables
    By Mike1500 in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 17th, 07:11 PM
  4. Service Pac Update Changed Settings
    By Paula in forum Windows XP/2000/ME
    Replies: 0
    Last Post: July 24th, 10:54 PM
  5. On opening a Form with SubForms records get changed
    By RBB in forum Microsoft Access
    Replies: 1
    Last Post: July 5th, 03:17 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139