Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Question Unanswered: SQL statement to sync data between 2 distinct tables

    Greetings,

    I am currently developing a PHP-based intranet system for my company... and using PEAR:DB as well. However, I am having troubles trying to create a generic SQL statement to sync all my records between 2 different tables in separate databases.

    Here is what I need to synchronize:


    MCINFO_table in Database1 --> Main table
    ====================
    Date * SN * MchNum * MchType * Location * Active
    ---------------------------------------------------
    02/26/01 * 12345 * SA23 * ASMAB339 * SC70 * D
    04/08/02 * 12345 * SA19 * ASMAB339 * SC70 * D
    02/17/03 * 12345 * SA08 * ASMAB339 * SC70 * A
    02/26/01 * 67890 * SA50 * ASMAB339 * SC50 * D
    02/17/03 * 67890 * SA34 * ASMAB339 * SC50 * A



    ENTITY_table in Database2 --> Backup table
    ===================
    Entity * EntityType * SerialNumber * Location
    --------------------------------------------
    SA08 * ASMAB339 * 12345 * SC70
    SA34 * ASMAB339 * 67890 * SC50


    To sum it up, I have a 'SYNC' button in my php page... whereby, once the user clicks on it, the system will synchronize any UPDATED or NEW data within MCINFO_table with ENTITY_table. In other words, any updated or new records within MCINFO_table (the main table) must be in-sync with ENTITY_table (the backup table).

    I will designate MCINFO_table as "M" and ENTITY_table as "E"..... And since both tables have varying fieldnames, these are the field mappings:

    M.MchNum = E.Entity
    M.MchType = E.EntityType
    M.SN = E.SerialNumber
    M.Location = E.Location

    When the SYNC button is pressed, I want the system to check EVERY ROW for any updated or new data in MCINFO_table where

    M.MchNum = E.Entity
    &
    M.Active = A
    &
    M.Date = between today's date & 2 days ago timeframe

    If these conditions are met, then perform the synchronization process with ENTITY_table.

    I've been unable to come up with the correct SQL statements for this sync process... can somebody please help me?... thank you so much :)

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    try:

    select 1.*
    from table_1 1
    , table_2 2
    where 1.pk = 2.pk
    and (1.col_1 <> 2.col_1
    or 1.col2 <> 2.col2
    ....
    )
    UNION all
    select 1.*
    from table_1 1
    where 1.pk not in
    (select 2.pk
    from table_2 2)

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    Posts
    20

    Question

    Thnx for the prompt reply evanhattem,

    I tried applying your sql statements into my code but when I clicked the Sync button in my php page.... it gives me a DB:Syntax error...

    FYI, I changed the table names to suit my purpose... for instance:

    table_1 => mcinfo
    table_2 => entity_tbl
    pk => mc_no (for mcinfo)
    pk => entity (for entity_tbl)

    Here is the sql statement that I applied:


    SELECT 1.*
    FROM mcinfo 1, entity_tbl 2
    WHERE 1.mc_no = 2.entity
    AND (1.mc_serial_no <> 2.serialnumber
    OR 1.mc_area <> 2.location)
    UNION all
    SELECT 1.*
    FROM mcinfo 1
    WHERE 1.mc_no NOT IN
    (SELECT 2.entity
    FROM entity_tbl 2)


    1) Anything wrong with the sql statement above ? is the NOT IN statement a generic sql statement ?... I thought it should be NOT EXISTS instead.

    2) And from what I understand, the sql above is just a query right ?

    3) Do I need to include additional sql statements (e.g. INSERT INTO) in order for the system to perform the actual synchronization of data between the two tables ?

    4) If yes for question 3, could u pls show me the INSERT INTO sql statement after running the sql query above (provided there's no errors hopefully) ?

    Thank you so much =)
    Last edited by nickcwj; 10-03-03 at 05:03.

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    sorry, some errors from me.
    Should be like :
    select 1.*
    from table_1 1
    , table_2 2
    where 1.pk = 2.fk_tab1
    and (1.col_1 <> 2.col_1
    or 1.col2 <> 2.col2
    ....
    )
    UNION all
    select 1.*
    from table_1 1
    where 1.pk not in
    (select 2.fk_tab1
    from table_2 2)

    Note that the primary key of tab1 should be mapped to the foreign key kolom in tab2.
    Nexet, try to replace the 1.* to 1.kol1, 1.kol2 etc.

    I believe that the NOT IN is a generic SQL statement, at least Oracleshould understand it. NOT EXISTS is a slighty different operator. This can be used if you want to. Code would like:


    select 1.*
    from table_1 1
    , table_2 2
    where 1.pk = 2.fk_tab1
    and (1.col_1 <> 2.col_1
    or 1.col2 <> 2.col2
    ....
    )
    UNION all
    select 1.*
    from table_1 1
    where not exist
    (select 1
    from table_2 2
    where 2.fk_tab1 = 1.pk)

    Ans yes this is just a query. If you want to do the insert directly to the table to be synced, use

    Insert into sync_table ( select ... union all ... select...)

    If you do have access in some way to a SQL prompt, you can try out your statements. You'd better check the syntax for oracle before trying in php, so you know what problems may occur.

    If any errors on SQLPLus prompt, pls provide the create scripts for the tables, or table definitons, so I can try it here.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Oct 2003
    Posts
    20

    Question

    Hi evanhattem,

    Great to hear from u again... now it makes more sense now... FYI, i am not using Oracle at the moment... i am using MySQL (for testing purposes on my local workstation) and will migrate to Sybase once my project is up and running... since i did mentioned I am using PHP and PEAR:DB... the PEAR:DB portion helps a lot in resolving database migration headaches ... especially the sql codes... that's why i'm using and requesting help for generic SQL statements instead of proprietary ones such as Oracle etc.

    One important thing to note is that since i'm testing my codes using MySQL as the db backend, MySQL unfortunately does NOT support foreign keys... that's the headache i'm facing.

    >> Is there a workaround for the sql statements since foreign keys are out of the question and has to be omitted from the example you have shown me ?

    Hear from you soon.... thnx :)

  6. #6
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    the fact that MySQL does not support FK's should not really matter. U use the values of the source table in the backup table and should be able to use it, but we'll have to rewrite the query to make sure we are looking at the right rows in the second table.

    What is the primary key in the source table and is this protected by a constraint and is this value used in the target table ?
    Edwin van Hattem
    OCP DBA / System analyst

  7. #7
    Join Date
    Oct 2003
    Posts
    20

    Arrow

    Hi edwin,

    I've attached a schema.zip file for your reference... inside the schema,

    1) entity_tbl = target table
    2) mcinfo = source table

    The only constraint is that synchronization will only be performed if the
    mcinfo.mc_no = entity_tbl.entity ... AND ... mcinfo.active = 'A' ... AND ... mcinfo.datetimestart = timeframe between current date and 2 days ago.

    Thnx and regards,
    Nick
    Attached Files Attached Files

Posting Permissions

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