3

I have two tables ie., Table1 and Table2. The only difference between these tables is that Table2 contains extra columns creation_date and last_updated_date. Every week, Table1 records gets updated/inserted. These changes need to be reflected in Table2. We use Table2 to get latest records using max(last_updated_date). So, we want a query to compare two tables and make necessary changes in Table2 based on Table1. Thanks in advance.

Naruto
  • 103
  • 1
  • 9
  • If a record was updated in table 1 and there was no timestamp saved, how would the query know of that updated record to update table2? Why not use Triggers – Hanky Panky Nov 21 '14 at 06:11
  • Hi Hannky. Thanks for Your comment.Table1 with 2k+ records and weekly once some fields of records get updated. Table2 also contains 2k+ records. Just we need to compare it with Table1 for changed records and need to update them and change last_updated_date. We are thinking to implement this requirement using PHP and Mysql. – Naruto Nov 21 '14 at 06:33
  • Why not add the columns to table 1? creation date can be current time and last updated can have an ON UPDATE clause to update when the row is updated! – barbiepylon Nov 21 '14 at 06:44
  • Hi BarbiePylon. Thanks for comment. Yes you are correct but our requirement is limited so we cant keep those two columns as you stated – Naruto Nov 21 '14 at 06:53
  • 2
    @Naruto So this is what you're doing after your series has ended? – Logan Wayne Nov 21 '14 at 07:27
  • If you need to update table2 with all the records which are in table1 but not in table2 than just fire a query resulting all those records which are not in table2. One more concern whether the primary key in both the table contains same value for same record ? – Bhavya Shaktawat Nov 21 '14 at 07:44
  • Can't you do something like `WHERE table1.id = table2.id AND (table1.colA != table2.colA OR table1.colB != table2.colB ...)` – DanielM Nov 21 '14 at 17:05

1 Answers1

1

Based on your query you can refer these links,hope it helps.

http://www.mysqltutorial.org/compare-two-tables-to-find-unmatched-records-mysql.aspx

Compare two SQL tables and return missing ids?

Community
  • 1
  • 1
Atif Shaikh
  • 281
  • 3
  • 7