pk2dpvp (6/6/2014)
If I change WHERE to AND it gives me this..:(408014 row(s) affected)
which with where is:
(2054 row(s) affected)
---------------------------------------------
Isn't this because it doesn't know what to do with AND so it affects every value found by "ON"? (Which is everything that did NOT change)?
It's because the outer join is converted to an inner join. You need to decide what you want to do here. My guess is you want an inner join, where non-matching rows would be unaffected by the update. If this is the case, write the join as an inner join and it doesn't matter where you put the filter.
Alternatively, if you want all rows to be affected by the update, with matching rows taking values from the second table and nonmatching rows taking NULL values, then write it as a left join with the filter in the join.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden