Update with from

1

A Developer wrote something like this:

create table #t1 
(ID int, value int)
go

create table #t2 
(ID int, value int)
go

insert into #t1
values (1000, null)

insert into #t2
values (1000, 5)
insert into #t2
values (1000, 10)

UPDATE	#t1
SET		#t1.value = #t2.value
FROM	#t1, #t2
WHERE	#t2.ID = #t1.ID

select * from #T1

drop table #t1

drop table #t2

I don't use a from in an update myself. In the select, I get the answer 5. But really, shouldn't this give an error? Is the answer undetermined?

2

Syntax is valid, although I would have used join (makes it alot easier to read, when dealing with many tables).

UPDATE #t1
   SET #t1.value = #t2.value
  FROM #t1
       INNER JOIN #t2
               ON #t2.ID = #t1.ID
;
3

Yes, the row result is undetermined. It is up to you to insure that only one value matches.

Also, critically, when using a JOIN in UPDATE, you should always use an alias for the table and UPDATE the alias name only -- otherwise SQL can "get confused" and give you an invalid result.

UPDATE	t1
SET    value = ca1.value
FROM	#t1 t1
CROSS APPLY (
    SELECT TOP (1) *
    FROM #t2 t2
    WHERE t2.ID = t1.ID
    ORDER BY t2.value --or whatever
) AS ca1
1 Like
4

We always use a FROM (and an ALIAS) because we find it easier to do a SELECT, in Debugging, to check the effect of the UPDATE, without changing the syntax of the Update statement (which might carry the risk that the change we make breaks something)

UPDATE T1
SET
-- SELECT TOP 100 T1.MyPKey, T1.OtherInterestingColumn,
	Col1 = T2.ColX,
	COl2 = T2.ColY
FROM Table1 AS T1
	JOIN Table2 AS T2
		 ON T2.MyPKey = T1.MyPKey
2 Likes