Comparing One Table Field to another Table Field

I'm using SQL Server 2005 Express.

(Below are the three tables along with their fields)

1) tblEmailAddress Table
a) EmailAddress
b) Emailid (each EmailAddress has this corresponding Emailid)

2) tblEmailGroupLink Table
a) Emailid
b) Unsubscribed

3) Remove_List Table

a) EmailAddress

What I'd like to accomplish is...

A) To compare the EmailAddress field of all the records in the Remove_list Table to the EmailAddress field of all the records in the tblEmailAddress Table and if there is a match, get the Emailid field value.

B) Go to the tblEmailGroupLink Table and lookup the Emailid and change the value of the Unsubscribed field to 'True' for that record.

I'd appreciate any help I can get.

Thanks,

Bill

Hi, one sample would be:

UPDATE tblEmailGroupLink

SET UnSubscribed = 0x1
From tblEmailGroupLink L

INNER JOIN
(
Select E.EMailid
FROm tblEmailAddress E
INNER JOIN Remove_List R

ON E.EMailAddress = R.EMailAddress

) SubQUery
ON SubQUery.EMailid = L..EMailid

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thank you for this Jens. Please keep in mind I'm very new at this, and am not sure of a few things.

Should I get into this database first, before I run a new Query? I did, but I want to be sure I'm doing this right, and when I run the Query this is what I got in a message.

Msg 207, Level 16, State 1, Line 11

Invalid column name ''.

Please let me know what you think... thanks Bill

|||You will have to be in the database where you want to change the values. The query had a typo:

ON SubQUery.EMailid = L.EMailid (only a single point after L)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks Jens, I'll give it a try.

Bill

|||

Jens,

Thanks, it works great.

Bill

sqlsql