#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    7
    Rep Power
    0

    Question Update table from one database with data from another database


    Hello I have 2 MySQL databases on the same server with same structure and same data. Occasionally data in one database are changed for 7 days and then should be back to the same value as in first database. Through PhpMyAdmin I am trying to write statement that should do thi but something I am missing. I have databases: db1_local and db2_local Table gitem column gbaseprice (should be synchronized from db1 to db2) column gitemcode (has same value in both databases and synchronizing should be followed with this parameter so that id's don't mess up) Appreciate any help

    Here is statement e.g. SQL statement that run in db2

    UPDATE gitem
    SET gbaseprice=db1_local.gbaseprice
    FROM
    db1_local.gitem(`gbaseprice`)
    INNER JOIN db1_local.gitemtable
    ON db1_local.gitem(`gitemcode`)=db2_local.gitem(`gitemcode`)
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,144
    Rep Power
    4274
    mysql does not use FROM syntax in a joined update...
    Code:
    UPDATE db2_local.gitem
    INNER 
      JOIN db1_local.gitem 
        ON db1_local.gitem.gitemcode = db2_local.gitem.gitemcode
       SET db2_local.gitem.gbaseprice = db1_local.gitem.gbaseprice

    Comments on this post

    • szkgroove agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by r937
    mysql does not use FROM syntax in a joined update...
    Code:
    UPDATE db2_local.gitem
    INNER 
      JOIN db1_local.gitem 
        ON db1_local.gitem.gitemcode = db2_local.gitem.gitemcode
       SET db2_local.gitem.gbaseprice = db1_local.gitem.gbaseprice



    you are right, it works fine
    thank you for effort

IMN logo majestic logo threadwatch logo seochat tools logo