Conditional Update of a field from multiple tables

Dear all,

I have a target table with two fields: Date and ID

There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score

Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.

Could anyone kindly help me or give me some hints? Thanks

Regards

Roscol