quote:

---

Originally posted by TG

updates and inserts are 2 different statements. You've got the 1st part already (the update on an inner join).
Now you have to do the Insert by using either "where NOT Exists" or "OUTER JOIN where <join column(s) of outer table> is NULL".

Be One with the Optimizer
TG

---

set nocount on
declare @tableA table (id int, col1 int)
declare @tableB table (id int, col1 int)
insert @tableA Select 1,1 union select 2,1
insert @tableB Select 2,2 union select 3,3

print 'before update'
Select * from @TableA

--update
update a set
	a.col1 = b.col1
from	@tableA a
JOIN	@tableB b ON a.id = b.id

print 'after update'
SElect * from @TableA

/*
--insert (where not exists)
insert @TableA (col1) 
Select col1
From	@TableB b
Where NOT Exists(Select * from @TableA where col1 = b.col1)
*/

--insert (left outer join)
insert @tableA (col1)
Select	b.col1
From	@TableB b
Left JOIN @TableA a
		ON a.id = b.id
Where	a.id is NULL


print 'after insert and update'
Select * from @TableA