Efficiently Merge values from table2 into table1

Jakobud

Look at the following SQLFiddle for reference: http://sqlfiddle.com/#!2/f02ca/1

I have two nearly identical tables. I want to determine the difference between the table data, update one table with the different data from the other table.

table1
|| *id* || *some_id* || *timestamp* ||
||    1 ||         A ||       01:00 ||
||    2 ||         B ||       02:00 ||
||    3 ||         B ||       01:00 ||
||    4 ||         A ||       02:00 ||
||    5 ||         A ||       03:00 ||

table2
|| *id* || *some_id* ||
||    1 ||         B ||  <-- some_id is different than table1
||    2 ||         B ||
||    3 ||         B ||
||    4 ||         A ||
||    5 ||         B ||  <-- some_id is different than table1

So I want to update table1, with new some_id‘s from table2. I can easily find the differences between the two tables:

SELECT t2.id
FROM table2 t2
LEFT JOIN table1 t1 ON t1.id = t2.id
WHERE t2.some_id != t1.some_id

Result:

1, 5 (the id's of the table2 rows that have a different some_id).

I thought I could do this type of query:

UPDATE table1
SET some_id = 
    (SELECT some_id FROM table2 WHERE id IN 
        (SELECT t2.id 
        FROM table2 t2
        LEFT JOIN table1 t1 ON t1.id = t2.id 
        WHERE t2.some_id != t1.some_id ))

I thought that query would update table1 with new some_id‘s from table2 but only for rows where the some_ids where different between them. But I get the following error:

SQL Error (1093): You can't specify target table 'table1' for update in FROM clause

Am I on the right track here? If so, how do I get around this problem? Is there a better or more efficient way up accomplishing this?

A couple notes:

1. It’s important that I do not just update or insert everything from table2 into table1, because of the timestamp column. That column will update itself when the row is updated. The only rows I want updated in table1 are the rows who’s some_id is different in table2. So essentially, the timestamp column is there to show when that row was last changed.
2. It’s important that this is done in as few queries as possible and that they are done efficiently. In my simple example here, there are only 5 rows per table, but in reality my tables have many thousands of rows.