SQLTeam.com | Weblogs | Forums

Can Update statement ever add more rows


Is it ever possible for an Update statement to add more rows to the table, despite the possibility that a join may result in more rows? ... Or will it just err as a result?


update t1
set t1.f1 = t2.f2
left join t2 on t1.f3 = t2.f3

(assume in the above example that the join statement really OUGHT to have an additional predicate, but is missing one, therefore, more than one row will be returned in the join).

Will it err, or will it actually ADD rows to the table, just from this Update statement?


That query will error. But if you switched it to a cross join, you'll still only update the rows that exist. It can't insert rows.

Here's a simple example:

create table #t1 (c1 int, c2 int)
create table #t2 (c1 int)

insert into #t1 values

insert into #t2 values

select * from #t1

select * from #t1 cross join #t2

update t1 set t1.c1 = t2.c1
from #t1 t1
cross join #t2 t2

select * from #t1

drop table #t1, #t2


Thank You very much for your help, Tara Kizer.


To append to what @TaraKizer said,

An UPDATE statement can never insert new rows.

When you use the UPDATE..FROM clause, you have to be careful. Go to this page, and look at the section "Best Practices" where they discuss the UPDATE..FROM construct.

The example you posted is one of the reasons why many people, @jcelko for example, recommend strongly against using that construct. It is not ANSI SQL. I use it, and use it often, but you just have to know what it is doing.

Couple of things you can do if you are not sure of how the update is going to work are

a) replace UPDATE t1 SET t1.c1 = t2.c1 with SELECT * and run the query. Then look at the t1.c1 and t2.c1. You can see what the existing value of t1.c1 is, and what it is going to be replaced with.

b) If it is a production environment, do a "BEGIN TRAN" before your update, examine the results (and number of rows affect) after the update, and if you are satisifed, run "COMMIT TRAN". If not, run "ROLLBACK TRAN". You have to be careful with this though because until you run COMMIT or ROLLBACK, other processes will not be able to access your table.