MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > MySQL Help
Reload this Page

Update table from one database with data from another database


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old October 26th, 2012, 05:59 AM
szkgroove szkgroove is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 7 szkgroove User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 57 m 28 sec
Reputation 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`)
Reply With Quote
  #2  
Old October 26th, 2012, 11:47 AM
r937's Avatar
r937 r937 is online now
SQL Consultant
Dev Shed God 43rd Plane (26000 - 26499 posts)
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,442 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 14 h 17 m 37 sec
Reputation Power: 4141
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!
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3  
Old October 26th, 2012, 05:11 PM
szkgroove szkgroove is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Oct 2012
Posts: 7 szkgroove User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 57 m 28 sec
Reputation Power: 0
Quote:
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
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MySQL Help > Update table from one database with data from another database

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap