1

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'
RayofCommand
  • 1,451
  • 8
  • 26
  • 36

3 Answers3

2

Try:

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'
Paul Michaels
  • 215
  • 1
  • 3
  • 12
2

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.

Supercoco
  • 21
  • 3
1

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'
andreszs
  • 709
  • 1
  • 6
  • 16
  • http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 don't know if this comes in sql 2012 but 2008 doesn't allow me to update 2 tables. – RayofCommand Feb 12 '14 at 14:24
  • Oh that really sucks, I didn't know because I only use MySQL. :) – andreszs Feb 12 '14 at 22:15