SQLTeam.com | Weblogs | Forums

Comparing columns in table to find where change occured


#1

Hi all, My two tables have similar data Table1 (Customer, Salesperson), Table2(Customer, SalesPerson, Change) Customer is the PK for Both Tables.

I need to Compare the Column Customer between Table1 and Table2 if any changes to the SalesPerson column in Table1, the column in Table2 -CHANGE needs to be updated to 1. How do I start the store procedure?

Thanks


#2

update t2 set change = 1 from table1 t1 inner join table2 t2 on t1.Customer = t2.Customer where t1.SalesPerson <> t2.SalesPerson


#3

Can SlaesPerson be NULL? If so you need slightly more complex logic than this, but if not you are good-to-go :smile:


#4

A salesperson can be null
if t1.salesperson is null and t2.salesperson is not then t2.salesperson should be changed to null as well. and change =1


#5

Something like this perhaps:

update t2
set change = 1
    , salesperson = CASE WHEN t1.salesperson is null THEN NULL ELSE t2.salesperson END
from
   table1 t1
inner join
   table2 t2
      on t1.Customer = t2.Customer
where
   t1.SalesPerson <> t2.SalesPerson
   OR (t1.SalesPerson IS NULL AND  t2.SalesPerson IS NOT NULL)
   OR (t1.SalesPerson IS NOT NULL AND  t2.SalesPerson IS NULL)