Careful with SQL server Update

Careful with SQL server Update:
The Blog is sharing my experience with the update statement in SQL server. We need to careful when write a update statement in SQL server like a scenarios below.
Initial setup:

create table A
( c1 int,
c2 int
)

create table B
( c1 int,
c2 int
)

insert into A values (101,0)
insert into A values (102,0)
insert into A values (103,0)

insert into B values (101,7)
insert into B values (102,5)
insert into B values (103,4)
insert into B values (103,6)

Tables with value:

select * from A


select * from B




The field in c2 is having Zero for all the fields. We need to update the field c2 in table A from the table B. But we have multiple values in table B for the value 103.


Based on the above scenario, the update statement has to fail. We mostly use the Inner join method of updates in SQL server Like a below statement.

begin tran
update A
set A.c2=B.c2
from A,B
where A.c1=B.c1
The above statement is updating the records. The above method is taking care of the multiple records.





The below method is giving the error message due to multiple records.

begin tran
update A set A.c2
=(select B.c2 from B
where A.c1=B.c1
)
We need to careful when we write a update statements.

Read More