CODEWITHSUNDEEP
sqloracle-sqldeveloper
GroovyGal
GroovyGal

Reputation: 37

Update a column for some rows in a table using another set of records from the same table

I have a table (Bank) with records like this --

BRANCH    | CITY        |  STATE     |  ZIP
-----------------------------------------------
Corporate |  City1      |  State1    |  Zip1
Corporate |  City2      |  State2    |  Zip2
Corporate |  City3      |  State3    |  Zip3
Corporate |  City4      |  State4    |  Zip4
Corporate |  City5      |  State5    |  Zip5
Corporate |  City6      |  State6    |  Zip6
1         |  City1      |  State1    |  
1         |  City4      |  State4    |  Zip4
1         |  City6      |  State6    |  Zip2
1         |  City10     |  State10   |  Zip10
2         |  City1      |  State1    |  Zip1
2         |  City2      |  State2    |  Zip2
2         |  City3      |  State3    |  Zip3
2         |  City5      |  State5    |  Zip4

Need to update column ZIP for Branch '1' using Branch 'Corporate' by matching 'City' and 'State'. It should not update record City10 of Branch 1 i.e. Zip should stay Zip10. Final result --

BRANCH    | CITY        |  STATE     |  ZIP
-----------------------------------------------
Corporate |  City1      |  State1    |  Zip1
Corporate |  City2      |  State2    |  Zip2
Corporate |  City3      |  State3    |  Zip3
Corporate |  City4      |  State4    |  Zip4
Corporate |  City5      |  State5    |  Zip5
Corporate |  City6      |  State6    |  Zip6
1         |  City1      |  State1    |  Zip1
1         |  City4      |  State4    |  Zip4
1         |  City6      |  State6    |  Zip6
1         |  City10     |  State10   |  Zip10
2         |  City1      |  State1    |  Zip1
2         |  City2      |  State2    |  Zip2
2         |  City3      |  State3    |  Zip3
2         |  City5      |  State5    |  Zip4

I used the following SQL but it is updating records of Branch 1 where City and State match are not found for Corporate.

Update Bank T1 
    set T1.zip = (select max(T2.zip) from Bank T2 where T1.city = T2.city and T1.state = T2.state and T2.branch ='Corporate' )
    where T1.branch = '1'

Upvotes: 1

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can use an explicit join:

update Bank b
    set b.zip = b2.zip
    from Bank b join
         (select city, state, max(b2.zip) as zip
          from Bank b2
          where b2.branch = 'Corporate'
          group by city, state
         ) b2
         on b2.city = b.city and b2.state = b.state and 
    where b.branch = '1';

You can also use your method, but you need to take into account non-matching values:

update Bank 
    set b.zip = (select coalesce(max(b2.zip), bank.zip)
                 from Bank b2
                 where b2.city = bank.city and b2.state = bank.state and b2.branch = 'Corporate'
                )
    where branch = '1';

However, that will unnecessarily update rows where the value does not change.

Upvotes: 2

Related Questions