Results 1 to 9 of 9
  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 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: SQL statement to sync data between 2 distinct tables

    Originally posted by nickcwj
    Greetings,

    I am currently developing a PHP-based intranet system for my company... and using PEARB 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
    Here a script to get you started...
    Hope this helps...

    /* ======= start ======= */
    USE TEST;
    DROP TABLE MCINFO_table;
    CREATE TABLE MCINFO_table (
    tx_date DATE,
    sn integer,
    mchnum char(4),
    mchtype char(8),
    location char(4),
    active char(1)) type=myisam;

    insert into MCINFO_table (tx_date, sn, mchnum, mchtype, location, active)
    values
    ('2001-02-26', 12345 , 'SA23' , 'ASMAB339' , 'SC70' , 'D'),
    ('2002-04-08' , 12345 , 'SA19' , 'ASMAB339' , 'SC70' , 'D'),
    ('2003-02-17' , 12345 , 'SA08' , 'ASMAB339' , 'SC70' , 'A'),
    ('2001-02-26' , 67890 , 'SA50' , 'ASMAB339' , 'SC50' , 'D'),
    ('2003-02-17' , 67890 , 'SA34' , 'ASMAB339' , 'SC50' , 'A'),
    ('2003-10-03' , 12555 , 'ZZ34' , 'ZSMAB339' , 'ZC50' , 'A');

    DROP TABLE ENTITY_table ;
    CREATE TABLE ENTITY_table
    (
    Entity CHAR(4),
    EntityType CHAR(8),
    SerialNumber INTEGER,
    Location CHAR(4)) TYPE=MYISAM;

    INSERT INTO ENTITY_table (Entity, EntityType, SerialNumber, Location) VALUES
    ('SA08' , 'ASMAB339' , 12345 , 'SC70'),
    ('SA34' , 'ASMAB339' , 67890 , 'SC50');

    DROP TABLE MY_TEMP;
    CREATE TEMPORARY TABLE MY_TEMP(
    Entity CHAR(4),
    EntityType CHAR(8),
    SerialNumber INTEGER,
    Location CHAR(4));

    /* Pick records that are to be synced and dump them into a temp table */
    INSERT INTO MY_TEMP
    SELECT M.MCHNUM, M.MCHTYPE, M.SN, M.LOCATION
    FROM MCINFO_table M
    LEFT JOIN ENTITY_table E ON (M.MCHNUM=E.ENTITY AND M.ACTIVE = 'A')
    WHERE M.tx_date >= (SUBDATE(CURDATE(), INTERVAL 2 DAY))
    AND M.tx_date <= (CURDATE());

    /* Now update the new or changed entries */
    REPLACE ENTITY_TABLE(Entity, EntityType, SerialNumber, Location)
    SELECT Entity, EntityType, SerialNumber, Location FROM MY_TEMP;
    DROP TABLE MY_TEMP;

    SELECT * FROM ENTITY_TABLE

    /* ====== end ====== */

    Hope this helps!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    very nice, bstjean

    i think you forgot the deletes, though

    nickcwj didn't explicitly mention deletes, but they are part of the general sync problem

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2003
    Posts
    20

    Thumbs up

    Your codes are awesome bstjean... simply awesome... i managed to learn something new from your codes. I'll keep you and r937 posted if there's anything... you two are my gurus!!!.... Kudos =)

    Best regards,
    Nick
    Last edited by nickcwj; 10-06-03 at 03:58.

  5. #5
    Join Date
    Oct 2003
    Posts
    20

    Question

    As stated by r937, could anyone here please show me the DELETE codes ... meaning whenever a record is deleted from the M table, it will also delete the corresponding record in E table when performing synchronization.

    Thnx :)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the deletes were included in the answer i gave you on, um, that other forum

    by the way, you should have mentioned that your target database is sybase and not really post into mysql and oracle forums, simply because the answers you get will likely include syntax that won't work in sybase, e.g. REPLACE/SELECT

    rudy
    Last edited by r937; 10-06-03 at 08:36.

  7. #7
    Join Date
    Oct 2003
    Posts
    20

    Question

    Hi rudy,

    If Replace/Select cannot be used in a sybase environment... what is the sql statement to best replace the following:


    REPLACE entity_tbl (entity, entitytype, serialnumber, location)
    SELECT entity, entitytype, serialnumber, location
    FROM temp_tbl
    And could you pls provide me a link to where I can learn all the SQL syntaxes (both generic and proprietary) ?

    And I tried implementing your DELETE statement together with bstjean's code .... but I couldn't get your DELETE portion to work. I get a lot of DB:Syntax errors after modifying and using your code.

    1) How would the DELETE sql statement be like in relation to bstjean's code?
    2) Where do I insert the DELETE statement inside bstjean's code?


    Please enlighten me...

    Thnx and regards,
    Nick
    Last edited by nickcwj; 10-06-03 at 21:45.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nick, i thought i gave it my best shot on sitepointforums

    one of the problems you have is that you posted the exact same question in seven different forums and you're now trying to merge all kinds of different answers

    i don't think i will try to write your queries for you, but rather, i will point you back to the answer i gave at sitepointforums and ask you to think about the queries i gave there, so that you understand the general strategy behind them, so that you will be able to write the actual queries yourself with the appropriate field names

    as far as learning sql, "both generic and proprietary", that's actually a career, not a short answer


  9. #9
    Join Date
    Oct 2003
    Posts
    20

    Unhappy

    Originally posted by r937
    nick, i thought i gave it my best shot on sitepointforums

    one of the problems you have is that you posted the exact same question in seven different forums and you're now trying to merge all kinds of different answers

    i don't think i will try to write your queries for you, but rather, i will point you back to the answer i gave at sitepointforums and ask you to think about the queries i gave there, so that you understand the general strategy behind them, so that you will be able to write the actual queries yourself with the appropriate field names

    as far as learning sql, "both generic and proprietary", that's actually a career, not a short answer

    ;)

    The reason I post the same question in several forums is because each forums have their own audiences... and I'll stand a better chance to get better answers if I ask a more varied audience. Previously, I've tried asking one question in only one forum... and believe me... that guy didn't even give me an answer despite my waiting patiently, just some irrelevant comment which I touted as a waste-of-my-time-playing-the-waiting-game. I was desperate for an answer back then coz I was really stuck, and the worst thing is nobody cared to answer me and if they did, they just give some silly comments. Geez... if I'm that knowledgeable and rich in working experience, I wouldn't be seeking help and advice in forums in the first place.... i'm fairly new to the working world... especially the web development arena.

    And one thing to point out is... even though I posted my same question in soooo many forums, I don't simply take a bit from here and there and apply it in my project.... in this context however, I am currently and solely using bstjean's current sql codes... coz it worked flawlessly for me... except for the DELETE portion in which I have tried using rudy's method but it won't work...

    This is my first time doing data synchronization using sql statements... that's why I've already tried my best and since I couldn't get things to work as it should, I was hoping someone here would help me out by directing me precisely what-to-code-in-sql ... then I'll be on my own from there...

    I hope you would understand... coz I'm the only one involved in developing this entire project... no team members, nothing... I sincerely apologize if I said anything to hurt, but I don't mean to.

Posting Permissions

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