I am pretty new to SQL and I already read something about joins , but coudn't find out. I painted my scenario here :

enter image description here

I want to update the Firstname of Table1 into Temp1 where the CreateTS of the matching ID in Table2 is < 08.02.2014

for that I need a join but can't solve it. Any Tips are welcome..

update Table1
set Firstname = 'Temp1'
where Firstame = 'xxx'

join Table
and CreateTS < '2014-02-08 15:00:00.000'


update Table1 t1
set t1.Firstname = 'Temp1'    
inner join Table2 t2 on
  t1.Id = t2.Id -- Whatever your PK and FK are here
where t1.Firstame = 'xxx'
and t2.CreateTS < '2014-02-08 15:00:00.000'

Here below you have the answer. To highlight the change, I inserted two FirstName as 'not_updated'. Please, see SQL_Fiddle

update Table1
join Table2 on Table1.id = Table2.id
set Table1.FirstName = 'Temp1'
where Table2.CreateTS < '2014-02-08  15:00:00.000';

I hope this helps.


I wouldn't use join for that. Try:

UPDATE Table1,Table2
SET Table1.FirstName = 'Temp1'
WHERE Table1.FirstName = 'xxx' AND Table1.ID = Table2.ID AND Table2.CreateTS < '2014-02-08 15:00:00.000'

Not the answer you're looking for? Browse other questions tagged or ask your own question.