Jenny O'Reilly Jenny O'Reilly - 4 months ago 10
SQL Question

SQL update statement with different table in from clause

Out of accident I noticed that the following query is actually valid:

UPDATE bikes
SET price = NULL
FROM inserted
WHERE inserted.owner_id = 123456


This is part of a trigger where someone forgot to join the original table to the
inserted
table. The result is that when the trigger is executed, all prices are set to
NULL
.

The correct SQL statement is this:

UPDATE bikes
SET price = NULL
FROM inserted
INNER JOIN bikes ON bikes.id=inserted.id
WHERE inserted.owner_id = 123456


How/why is this first statement valid?

Answer

Why wouldn't it be valid? SQL Server doesn't know what you're trying to do. It thinks you want to update all of the fields where some condition exists on another table. See the last update below.

SETUP

declare @table table
(
  id int,
  name varchar(10)
)
declare @itable table
(
  id int,
  name varchar(10)
)

insert into @table (id, name)
select 1,'abc' union 
select 2,'def' union 
select 3,'ghi' union 
select 4,'jkl' union 
select 5,'mno' union 
select 6,'pqr'

insert into @itable (id, name)
select 1,'abc' union 
select 2,'def' union 
select 3,'ghi' union 
select 4,'jkl' union 
select 5,'mno' union 
select 6,'pqr'

All names on @table will change to zzz

update @table
set name = 'zzz'
from @itable i
where i.id = 1

select * from @itable
select * from @table

All names where id = 1 on @table becomes yyy

update @table 
set name = 'yyy'
from @itable i 
inner join @table t on i.id = t.id
where i.id = 1

select * from @itable
select * from @table

This will NOT update anything

update @table
set name = 'aaa'
from @itable i
where i.id = 133

select * from @itable
select * from @table