SQL Server Update Query -- sql server (microsoft)

cilevac NO[at]SPAM yahoo.ca
2/20/2007 11:14:40 AM

I have an SQL database table that I need to perform some updates on.

What I am trying to do is update one field in one table based on the
information found in another table. I can run the query in Access, but
I need to do this in SQL Server so that I can schedule it, either in a
stored procedure or at T-SQL command.

Here is what I have tried thus far, both giving me errors:

UPDATE iaa_members
SET iaa_members.astin = '1'
WHERE (iaa_members.id_number = IAA_MULTIPLE_2006.id) AND
IAA_MULTIPLE_2006.astin = '1'

Error:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'IAA_MULTIPLE_2006' does not match with a table name
or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'IAA_MULTIPLE_2006' does not match with a table name
or alias name used in the query.

UPDATE iaa_members INNER JOIN IAA_MULTIPLE_2006 ON
iaa_members.ID_NUMBER = IAA_MULTIPLE_2006.ID
SET iaa_members.ASTIN = 1
WHERE (((IAA_MULTIPLE_2006.ASTIN)=1));

Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'INNER'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'IAA_MULTIPLE_2006'.

Any idea what I can do to get this working?

DevilsFan1973
2/20/2007 11:55:28 AM

[quoted text, click to view]

Please ignore. I got an answer already.

UPDATE iaa_members
SET iaa_members.astin = '1'
WHERE iaa_members.id_number in (select IAA_MULTIPLE_2006.id from
IAA_MULTIPLE_2006 where IAA_MULTIPLE_2006.astin = '1')

sql server (microsoft) : SQL Server Update Query