Data synchronization between tables in mysql

Question

I have two tables (let's call them A and B) with same structure and i need to synchronize data in them...

There's one primary key field, with same value in both tables, and several fields with value in table A and null (or obsolete value that need to be replaced with current value from table A) in table B... I need to copy value from table A to table B.

Is there any easy way (other than replication) to do this in mySQL 4.1?

thanks in advance

JOIN 2 tables on the primary key and issue an UPDATE query? — N.B.
– N.B., Commented Jan 11, 2012 at 13:07
use LEFT JOIN to identify your NULL records in the second table then use dev.mysql.com/doc/refman/4.1/en/insert-select.html as answered below — Sergey Benner
– Sergey Benner, Commented Jan 11, 2012 at 13:34
@SergeyBenner actually, i made an error in question... some fields have null value, some has obsolete value that need to be replaced so your answer won't work, but thanks anyway.... — onedevteam.com
– onedevteam.com, Commented Jan 11, 2012 at 13:55
Check INSERT IGNORE or INSERT...ON DUPLICATE KEY UPDATE statements for your merging needs. Check also dev.mysql.com/doc/refman/4.1/en/replace.html REPLACE INTO — Sergey Benner
– Sergey Benner, Commented Jan 11, 2012 at 14:02

Uday Sawant · Accepted Answer · 2012-01-11 13:34:58Z

2

Try this -

UPDATE table_b b, table_a a 
SET b.field1 = a.field1, b.field2 = a.field2  
WHERE b.primary_key = a.primary_key

add the fields as required.

answered Jan 11, 2012 at 13:34

Uday Sawant

5,8083 gold badges35 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

adman_9000 · Accepted Answer · 2012-01-11 13:09:34Z

0

Can you just do:

INSERT INTO table1 (field1,field2,field3) SELECT field1,field2,field3 FROM table2;

Or do you actually already have data in table2 and you need to update it rather than insert new columns?

answered Jan 11, 2012 at 13:09

adman_9000

493 bronze badges

Collectives™ on Stack Overflow

Data synchronization between tables in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related