Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Script Library  complex update

Author  Topic  savatrak Starting Member 1 Post Posted - 2010-10-26 : 17:34:30 hello. I have two tables A and B. Table A holds bank collaterals records having to do with bank loans. Each bank collateral has an owner and an issuer. You do not need to know more about them. Just this. Each one of them have a bank account and for that reason a bank id has been assigned to them. This bank id is generated from the system that bank uses which is called Profits. So each of them (owner/issuer) has a profits id (unique). So table A has many records and each one has an owner profits id and an issuer profits id. Now there is a routine which is executed once a year and is called data cleansing. There is a possibility for a bank customer to have more than one profits id's accidentally. Here comes data cleansing to exterminate clones for a customer. e.g a cstomer has id's 2343435, 0090909, 9209320. In database seems that because there are 3 different profits ids there will be 3 customers. Here comes data cleansing which finds out that 9209320 and 0090909 are clones of same customer and that the 3 ids are one and only: the 2343435. Data Cleansing updates another table B and specifically updates each profits id with a new value. In our previous example we will have : Before DataCleansing updates B B ---------------------- ProfitsId_ex_value ProfitsId_new_value 2343435 2343435 0090909 0090909 9209320 9209320 After DataCleansing updates B B ---------------------- ProfitsId_ex_value ProfitsId_new_value 2343435 2343435 0090909 2343435 9209320 2343435 in table A I have A ----------------------------------- OwnerProfitsId IssuerProfitsId 0090909 9209320 I need to update those values in table A that exist in table B and have new value (ProfitsId_new_value <> ProfitsId_ex_value ) Is it possible with only one update sql statement? No fetch no cursor use. thanks