struggling with update query based on inner join in SQL 2008 (1 Viewer)

[joe789]

Hi Folks,

I have been struggling with a query that should not be this difficult ... it is essentially updating a value based on a inner join ... any help would be greatly appreciated:

Table1
LastName
FirstName
DateOfBirth
UniqueID

Table2
LastName
FirstName
DateOfBirth
UniqueID

Basically, I want to update the value in Table1.UniqueID with the value in Table2.UniqueID based on Table1.LastName = Table2.LastName AND Table1.FirstName = Table2.FirstName AND Table1.DateOfBirth = Table2.DateOfBirth

This is what I have tried including modifications of it from the help in various areas of the web and SQL itself and it just doesn't work:

update Table1 set UniqueID = Table2.UniqueID
from Table1 Table2
inner join Table2 on
Table1.LNAME = Table2.LNAME
Table1.FNAME = Table2.FNAME
Table1.DOB = Table2.DOB

The result just doesn't work it renders some type of incorrect syntax when I cannot find any instances of any type?

Thanks!

 

[WayneRyan]

Joe,

update Table1 
set    Table1.UniqueID = Table2.UniqueID
from   Table1, Table2
Where  Table1.LNAME = Table2.LNAME And
       Table1.FNAME = Table2.FNAME And
       Table1.DOB = Table2.DOB

Wayne

 

[SQL_Hell]

or

UPDATE Table1 
SET UniqueID = Table2.UniqueID
FROM Table1
INNER JOIN Table2 ON
Table1.LNAME = Table2.LNAME AND
Table1.FNAME = Table2.FNAME AND
Table1.DOB = Table2.DOB