RE: SQL UPDATE question

[David FOXWELL <David.FOXWELL@xxxxxxxxx>]

Thanks Rob and Charles.

I wonder if someone could explain this UPDATE request to me?

When updating columns of one table from another, I always have to search for this example, as I can never remember it. It would certainly help if I could understand the different stages of the request.


UPDATE MasterFile M

SET FieldToUpdate =
  (SELECT T.MyField FROM TransactionFile T WHERE T.KeyField = M.KeyField )

WHERE M.type= 'R' AND
      M.KeyField IN (SELECT T.KeyField FROM TransactionFile T)

I've had problems where the several row are returned and so the update fails.



Thanks.