Question about updating a table using data from another table

Let's say I have table A with ID as it's primary key field. Then I have table B which has the data I want to copy over to table A.
What happens if Table B has more than 1 record for a specific ID but different values in the other columns? Which record will ultimately win? I mean what logic does the engine use to determine which one will be updated last?

I think it updates with the first record that matches. There is no guarantee though.

[quote user="dineshasanka"]
You will get an error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.[/quote]
It will depend on the way the query was written. If the query was written as UPDATE.. SET = (SELECT ...) then yes. IF the query uses a JOIN then not.

Wow a lot of responses since I last checked.
First, I'm not using a subquery so there's no error. I'm actually joining the tables. I'm responsible for loading data from another (progress) database into my sql server 2005 database. The progress side is giving me all the changes in one recordset with an extra column to indicate whether it's an insert, update or delete. The inserts and deletes are easy. It's the updates I'm concerned about. If let's say in between the times I retrieve the data there is more than one update to a single record. How will that be treated in the following statement?
update dbo.activity
set action = pa.action, last_change_date = pa.last_change_date
from dbo.activity a inner join progress_activity pa on a.activity_id = pa.activity_id
The primary key of my table (activity) is activity_id. The primary key of progress_activity is activity_id, last_change_date

Disagree: the update will take into account each matching row, and the end result will be from the last matching row that happens to be found ...
In the following script, change the order of the insert statements, and check the result:
create table #t1 (i int, a varchar(1))
create table #t2 (i int, a varchar(1))
insert into #t1 values (1, null)
insert into #t2 values (1, 'a')
insert into #t2 values (1, 'b')
insert into #t2 values (1, 'c')
update #t1 set #t1.a = #t2.a
from #t1 inner join #t2 on #t1.i = #t2.i
select * from #t1
drop table #t1
drop table #t2

Don't you love it when they start behaving consistently? It's so refreshingly unexpected!

[quote user="Adriaan"]
Disagree: the update will take into account each matching row, and the end result will be from the last matching row that happens to be found ...
In the following script, change the order of the insert statements, and check the result:
create table #t1 (i int, a varchar(1))
create table #t2 (i int, a varchar(1))
insert into #t1 values (1, null)
insert into #t2 values (1, 'a')
insert into #t2 values (1, 'b')
insert into #t2 values (1, 'c')
update #t1 set #t1.a = #t2.a
from #t1 inner join #t2 on #t1.i = #t2.i
select * from #t1
drop table #t1
drop table #t2
[/quote]
Adriann
You mean the first record? I ran your code and I always see 'a' in t1. I changed the order to put 'b' first in the Insert and I see the value of 'b' in the table t1.

I was testing in SQL 2000, where this script updates to the last value inserted into the temp table.

[quote user="Madhivanan"]
As said, it would update first matched record
Why dont you test and and confirm?
[/quote]
I have run some tests and it looked like it used a consistent pattern in that it was updated with the first record based on a specific sort order. So then my next question would be are we able to control the sort order? Obviously we can declare a sort order in the update statement. Perhaps I can use a subquery which specifies a sort order. I'll test that and let you know.