compare two same tables find the records that is different

blackhorse

say I have a table called 'member' another table 'temp_member", they have the same structure.

Each of them have hundreds of thousands records.

1) How can I update 'member' table with the 'temp_member' table value by matching the id,

I used this, but it is too slow for hundreds of thousands records

UPDATE member, temp_member

member.first_name = temp_member.first_name,
    member.last_name = temp_member.last_name,
    member.middle_name = temp_member.middle_name

WHERE member.id = temp_member.id

Another approach is that

2) How can I find ids in 'member' that has been updated in 'temp_member'? If I can do it in a best performance way, then I can delete the old records in 'member' and select the new data from 'temp_member' and insert it back to 'member'.

here is the left join approach to find different records between two tables. Is there any other better performance approach?

SELECT tb1.id FROM tb1 LEFT JOIN tb2 ON (tb1.id1=tb2.id1 AND tb1.id2=tb2.id2) WHERE tb2.id1 IS NULL

Thanks!

bradgrafelman

blackhorse wrote:
Is there any other better performance approach?

Don't have two separate tables to begin with? That'd be your best option, considering you wouldn't be doing this compare-and-update at all. 🙂

Otherwise, maybe you could have an "updated" table with two fields: id of member with updated data, and a timestamp of when that update occured. Then you'd run the UPDATE query only on the records that were changed on or before the current timestamp when you begin the process.

blackhorse

No. I don't control the how many tables I can have.

I read these dozens thousands records from 3rd party xml to create my original table.

Then everyday, I would have to read in the same set records, create the temp table, compare with the original table. deactive the records that are not in the new reading, update the records if it is different in the new reading, add new records from the new reading, and keep the rest not changed records untouched.

I cannot just dump the original table and recreate the table from new reading. Because I need to keep the timestamp in the original table to tell when the record is updated.