Make a backup instead I did not understand exactly what you meant.
UPDATE (yourTable INNER JOIN twoFieldsTable
ON yourTable.id1=twoFieldsTable.id1)
INNER JOIN yourTable AS b
ON b.id2=twoFieldsTable.id2
SET yourTable.SomeField = b.SomeField
Basically, part of your table updates ... part of your table. To avoid
confusion, I used an alias to describe the part that will supply the values
in the updating process, I used the alias 'b'. I assumed your table with
two fields is called twoFieldsTable, and with that, it becomes almost
evident that b.id2=twoFieldsTable.id2 will be the records supplying data,
and yourTable.id1=twoFieldsTable.id1 will be the records being updated.
----------------------
Note 1 - : nothing seems to forbid duplication in your twoFieldsTable:
ID1 ID2
001
002
003
004 003
005 002
006 001
so the record with id1=006 would be updated twice (keeping only the LAST
updating value it will 'see') and will behave, in this case like, in VBA:
Dim i As integer
i=1
i=2
So, it could be nice to add a no-dup index on the pair of fields (ID1, ID2)
to avoid ... surprises.
--------------------
Note 2- : It won't handle recursion the way you may expected.
ID1 ID2
001
002 001
003 002
004 002
It is not sure if the row ID=003 will get the initial values of 002 (ie,
before 002 get updated itself) or the initial values of 001 (ie, after 002
get updated). Furthermore, 003 and 004 may be updated differently (ie, one
with values before 002 get updated, and one after 002 get updated).
A no dup index on ID1 and on ID2 may help to avoid such problem, but will
not care of that problem entirely.
