Help with Update statement

N

Net

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,
 
G

George Nicholson

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,
 
Top