update_join_statements.sql

declare @table1 table (date_field date, value_field varchar(25))

insert into @table1 (date_field, value_field) values
  ('4/1/2012', 'intial value 1'),
	('4/2/2012', 'intial value 2'),
	('4/3/2012', 'intial value 3'),
	('4/4/2012', 'intial value 4')
	
select * from @table1

declare @table2 table (date_field date, value_field varchar(25))

insert into @table2 (date_field, value_field) values
	('4/1/2012', 'modified value 1'),
	('4/2/2012', 'modified value 2'),
	('4/3/2012', 'modified value 3'),
	('4/4/2012', 'modified value 4')
		
UPDATE t1
  SET t1.value_field = t2.value_field
FROM @table1 t1
  JOIN @table2 AS t2
    ON t1.date_field = t2.date_field
    
-- or use a subquery

UPDATE t1
  SET t1.value_field = t2.value_field
FROM (select date_field, value_field from @table2 ) t2
  JOIN @table1 AS t1
    ON t1.date_field = t2.date_field


select * from @table1