Help with Update statement

Here is an Update statement:

UPDATE Sample, Main
SET Main.State = Sample.State, Main.ST = Sample.ST
WHERE Main.BU=Sample.BU And Main.Desc=Sample.Desc ;

How come the update is not working correctly?
For some reason it updates with the values from the first row and not on the
Where clause?

Thanks in advance,

1) You need to JOIN your tables. I think you have JOIN and WHERE confused.
WHERE acts as a Filter, and I don't see that a filter is required. JOIN
(which you do need and is missing entirely from your SQL) describes the
"connection" between the tables.

2) I don't see that you are updating anything in Sample, just using it as a
datasource, so it shouldn't be in the UPDATE clause, but you do need to JOIN
it.

My guess is you want something more like:

UPDATE Main
INNER JOIN Sample ON (Main.BU = Sample.BU) AND (Main.Desc = Sample.Desc)
SET Main.State = Sample.State, Main.ST = Sample.ST

That should update the State and ST fields in Main with the corresponding
values from Sample for all records having matching BU and Desc entries in
both tables.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Net" wrote in message
...
Here is an Update statement:

UPDATE Sample, Main
SET Main.State = Sample.State, Main.ST = Sample.ST
WHERE Main.BU=Sample.BU And Main.Desc=Sample.Desc ;

How come the update is not working correctly?
For some reason it updates with the values from the first row and not on
the
Where clause?

Thanks in advance,