Question of the month July 2016 – How does this UPDATE work?

Consider the following dataset


create table #t1(id int, amount money)
create table #t2(id int, tamount money)

insert into #t1(id,amount)
select 1,10 union all
select 2,20 union all
select 3,30 

insert into #t2(id,tamount)
select 1,55 union all
select 2,44

Now consider the following UPDATE statement

update #t1
set
 amount=tamount
from #t2

What do you think the result is? How does this UPDATE work despite not relating the tables #t1 and #t2?

Advertisements

4 thoughts on “Question of the month July 2016 – How does this UPDATE work?

  1. Before i actually running your provided query in SSMS, I guessed the output that first row of table t2 update all 3 rows of table t1.
    So for the sake of confirmation, i ran the query in SSMS. But i was surprised when i see the “Message” tab which shows the 2 messages. The first message is – “3 rows affected” and the second message is – “1 row affected”. I believe that first message is for table #t1 which have 3 rows. But what about second message? Am i missing something ?
    Madhivanan – It would be great if you could explain it in detail like what it internally does and so on.
    BTW really nice question it is.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s