Problem with update query (RESOLVED)

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

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

In any case, would this work?

Code:

---

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.

Thanks. I get this error:

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

Here is the solution:

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

I would normally write like this:

sql Code:

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

You could also try this.

Code:

---

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

---