CODEWITHSUNDEEP
sql-server
AdityaUbale
AdityaUbale

Reputation: 45

Answer me with the update statement

I have 101 records. and the prefix is a column. [Insurance phone] is like 589-652-5256 and I have mistakenly done like 

Substring ( EE.[Insurance Phone],4,3)

which result as -65 and I wanted it as 652 So I am writing update statement for same. I have different different phone number for 101 records. When I do a select statement I got each row's middle 3 number right, but if I use update statement then 625 [1st record of 101 record] number gets updated to all 101 records. Below are the select and update statements.

select 
    IC.COMPANY_NAME, EE.[Insurance Name], 
    substring (EE.[Insurance Phone], 5, 3) as phone
from
    Insurance_Companies_28012020 EE 
join 
    INS_COMPANIES IC on EE.[Insurance Name] = IC.COMPANY_NAME 
where 
    ic.IS_ACTIVE = 1 
    and [Insurance Phone] is not null

update BUSINESS_PHONE_NUMBERS 
set PREFIX = substring(EE.[Insurance Phone], 5, 3)
from Insurance_Companies_28012020 EE 
join INS_COMPANIES IC on EE.[Insurance Name] = IC.COMPANY_NAME 
where ic.IS_ACTIVE = 1 and [Insurance Phone] is not null

Upvotes: 0

Views: 69

Answers (2)

AdityaUbale
AdityaUbale

Reputation: 45

3o..., Thanks for your answer it helped me to get to the exact destination. As I said there is no relation between ee and t1. So I need to change your update statement as below. And it worked fine for me. 

update  t1
set     t1.PREFIX = substring(EE.[Insurance Phone], 5, 3)
from    BUSINESS_PHONE_NUMBERS t1
inner join INS_COMPANIES IC on IC.INS_COMPANY_ID = t1.OWNER_ID -- here you should link these two tables
inner join Insurance_Companies_28012020 EE on EE.[Insurance Name] = IC.COMPANY_NAME 
where   ic.IS_ACTIVE = 1
        and [Insurance Phone] is not null

Upvotes: 0

Max Zolotenko
Max Zolotenko

Reputation: 1132

I guess your query should look like:

update  t1
set     t1.PREFIX = substring(EE.[Insurance Phone], 5, 3)
from    BUSINESS_PHONE_NUMBERS t1
inner join Insurance_Companies_28012020 EE on ee.KEY_COLUMN = t1.KEY_COLUMN -- here you should link these two tables
inner join INS_COMPANIES IC on EE.[Insurance Name] = IC.COMPANY_NAME 
where   ic.IS_ACTIVE = 1
        and [Insurance Phone] is not null

Edited:

Your update statement doesn't link tables BUSINESS_PHONE_NUMBERS and Insurance_Companies_28012020, which produces a Cartesian product in the execution plan, which leads to the strange behaviour of the end update result. You get the situation: you need to update N rows, but you have N*M rows with all possible combinations. It's hard to say which one of them will be selected to update. In your case, it is the first record from Insurance_Companies_28012020 table.

Also, in your UPDATE statement you can add the condition to WHERE clause: BUSINESS_PHONE_NUMBERS.KEY_COLUMN = EE.KEY_COLUMN. This should give the same result as in my UPDATE.

Upvotes: 1

Related Questions