MySQL: Code To Compare 2 Tables, Then If row is the same update Master Date

Question

I have two tables, they are called MASTER and NEWDATA. The schema on both tables is exactly the same and they contain the field names: CODE, SERIAL, MODEL, VALIDATED_DATE.

I want to check if the values in the CODE field exists in the NEWDATA table and in the MASTER table. If they are in both tables, then I want to update the VALIDATED_DATE to now() in the MASTER table.

I have lots of records so I am curious what is the fastest way to do this?

Thanks!

Answer 1

UPDATE MASTER M
   Set VALIDATED_DATE = Now()
 WHERE EXISTS (SELECT NULL 
                 FROM NEWDATA AS ND 
                WHERE ND.CODE = M.CODE)

Answer 2

update master m inner join newdata n on n.code=m.code set m.validated_date = now()

The code column should be indexed in order for this to work efficiently. Given that it should be fast, much faster than the subquery method that others have suggested (unless you're running mysql 5.4, mysql's correlated subquery execution is basically totally unoptimized).

Answer 3

If you set your VALIDATED_DATE to reset itself to the current time on update, then a simple update of any column (given an index on CODE) should be pretty quick.