Problem with update query (RESOLVED)

[MikeGarvin]

Hi

I am trying to build an update query but keep getting an error that says the column TableB.FirstName does not exist:

update TableA set TableA.emailaddress = TableB.emailaddress where
TableA.firstname = TableB.FirstName and TableA.lastname = Tableb.lastname

Any ideas? Many thanks.

Mike

[Blakk_Majik]

Why do you have two tables with the same data? Is one an archive/backup of some sort?

In any case, would this work?

Update TableA set emailaddress =
(Select TableB.emailaddress
 From TableA, TableB
 Where TableA.firstname = TableB.firstname
 And TableA.lastname = TableB.lastname)

If not, someone more experienced may be able to help.

[MikeGarvin]

Thanks. I get this error:

You can't specify target table 'TableA' for update in FROM clause

[MikeGarvin]

Here is the solution:

UPDATE tablea INNER JOIN tableb ON (tablea.lastname = tableb.lastname) AND (tablea.firstname = tableb.firstname) SET tablea.emailaddress = tableb.emailaddress

[GaryMazzone]

I would normally write like this:

Update TableA Set 
  TableA.EmailAddress = TableB.EmailAddress 
Inner Join TableB On
  TableA.FirstName = TableB.FirstName And 
  TableA.LastName = TableB.LastName

[dee-u]

You could also try this.

UPDATE TableA SET 
  TableA.EmailAddress = TableB.EmailAddress 
FROM TableA INNER JOIN TableB ON
  TableA.FirstName = TableB.FirstName AND 
  TableA.LastName = TableB.LastName