CODEWITHSUNDEEP
sqlsql-server-2008
windowsgm
windowsgm

Reputation: 1616

Inconsistent Result - Update versus Select

I'm running the below statement and can't understand why the update affects 0 rows while a select with the same join and where return 1 row. They are both of type Varchar and crmnumberAC is bigger so should be able to take the string from AccountNumber. Thoughts?

begin transaction
update c
set c.crmnumberAC = a.AccountNumber
--select a.name, a.AccountNumber, c.fullname, c.crmnumberAC 
from Contact as c
right join Account as a
    on c.PFH_Mapping_Ac_ContacId = a.AccountId
WHERE (a.AccountNumber IS NOT NULL AND c.crmnumberAC IS NULL) 
    OR a.AccountNumber != c.crmnumberAC
rollback transaction

This is the result set when I uncomment the select and just run that as far as the where;

enter image description here

Upvotes: 2

Views: 58

Answers (1)

mucio
mucio

Reputation: 7119

As you can see, your SELECT doesn't return anything from the table Contact, the table you are trying to UPDATE.

The SELECT returns a row just because you have a right join, but in table Contact you have no row that fulfill the where condition.

Upvotes: 5

Related Questions