0

I am having a table A and table B.

Table A is created from Table B ( and few other table join operation ). Table A has all of its column which are subset of column in table B.

There is a column called as check_sum in table A and table B. This is basically a calculated column and if any column value changes then check_sum ( calculated value ) changes as well. For example:

Table A ( schema ):
cust_id ( pk ), cust_name, cust_location, check_sum ... other columns ( no need to worry about them )

Table B ( schema ) :
cust_id ( pk ), cust_name, cust_location, check_sum

Initially table B and A have entries like below:

Table A:  ( sample record )
1, John, USA, abc222abc, ... other columns

Table B:  ( sample record )
1, John, USA, abc222abc

Now lets say John changes his country location to UK, then corresponding entry in TABLE A looks like this

Table A:  ( sample record )
1, John, UK, checkSumChanged, ... other columns

Now i need to update my table B accordingly, so that instead of location of John as USA it should have it as UK. Column checksum is helpful here, since its value changes in Table A if any column changes.

This is the part i am stuck at. Not able to update just "CHANGED" rows from Table A to Table B. I have following query below. It is updating all rows instead of just the changed rows.

Here is the query.

UPDATE tableB 
SET 
    tableB.cust_name = tableA.cust_name, 
    tableB.cust_location = tableA.cust_location,
    tableB.check_sum = tableA.check_sum
FROM 
    tableA inner join tableB 
    on tableA.cust_id = tableB.cust_id
    and tableA.check_sum != tableB.check_sum

Any ideas or suggestion how can i correct my query to just update the changed record.

Thanks in advance!!!

user1188611
  • 945
  • 2
  • 14
  • 38

1 Answers1

2

Do it without a join:

update B
SET cust_name = a.cust_name, checksum = a.checksum, cust_location = a.cust_location
FROM a
WHERE a.custid = b.custid AND a.checksum != b.checksum;
krokodilko
  • 35,300
  • 7
  • 55
  • 79