Update Behavior - Mutliple Matching Rows

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

SQL Server 2000 Forums

SQL Server Development (2000)

Update Behavior - Mutliple Matching Rows

Author

Topic

atulkukreja
Starting Member

13 Posts

Posted - 2004-08-19 : 10:07:33

Please refer to te tables below.
When running an update query such as:
Update TableA set Salary = TableB.Salary from TableA, TableB where TableA.EmpName = TableB.EmpName

What is the default behavior of the SQL-Server engine: For employee Paul, there are multiple rows in TableB, which value is finally used for the update?

Does the update stop after finding the first match? This becomes cruicial if dealing with much larger tables with several 100,000 rows.

TableA:
=================
EmpName Salary
------- ------
Paul 30000
Jim 20000

TableB:
=================
EmpName Salary
------- ------
Paul 10000
Jim 40000
Paul 11000
Paul 35000
Paul 45000
.....

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-08-19 : 10:16:03

I think it will update the last record.. see for yourself.
use tempdb
go

create table TA (tid int, sal int)
insert into TA values (1, 1000)
insert into TA values (2, 2000)

create table TB (tid int, sal int)
insert into TB values (1, 33)
insert into TB values (1, 43)
insert into TB values (1, 53)
insert into TB values (2, 143)
insert into TB values (2, 243)
insert into TB values (2, 343)

Select * from TA
select * from TB

Update TA set TA.sal = TB.sal
from Ta, TB where Ta.tid = tb.tid

select * from TA

drop table ta
drop table tb

- Sekar

SamC
White Water Yakist

3467 Posts

Posted - 2004-08-19 : 10:18:19

The final value of Paul's salary will be the last value taken by the SQL engine at execution time. Since there's no order implied, expressed or warranteed, it could be any of the values from TableB that belong to Paul.

A better approach would be to design the UPDATE to return one salary for Paul. Here's an example that will return the largest Salary:

UPDATE A
  SET A.Salary = B.MaxSalary
  FROM TableA A
  INNER JOIN (
    SELECT EmpName, MAX(Salary) As MaxSalary
      FROM TableB 
      GROUP BY EmpName
  ) B ON A.EmpName = B.EmpName

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-19 : 10:47:59

one has to wonder if this is good database design....

Go with the flow & have fun! Else fight the flow :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-19 : 11:18:33

you should NEVER run an update statement like your original.

What are you trying to do? what salary, out of the multiple salaries, are you trying to store in your other table?

- Jeff

atulkukreja
Starting Member

13 Posts

Posted - 2004-08-19 : 13:11:57

Thanks to all those to responded.

I am fully aware of the Pitfalls of an update with multiple matching rows and the poor database deign implied in my question.

The idea of this conceptual question is to understand the workings of the SQL engine, specially if a Cartesian product is indeed created in such an update or is the update completed with the first macthing record.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources