try UPDATE tblCouncilEmailGroup SET tblCouncilEmailGroup.MemberID = tblCurrentCouncilMembers.Member_ID FROM tblCurrentCouncilMembers INNER JOIN tblCouncilEmailGroup ON tblCouncilEmailGroup.DONEID = tblCurrentCouncilMembers.DONE_ID WHERE tblCouncilEmailGroup.DONEID = tblCurrentCouncilMembers.DONE_ID
By using the Inner Join you eliminate records that have null DONE_ID in either table. The Where clause really shouldn't be needed but I always use to just make sure especially in db's I don't work with too often. Also I didn't use an editor so check for syntax and spellings. ~Terry -----Original Message----- From: Bruce Sorge [mailto:[EMAIL PROTECTED] Sent: Friday, March 23, 2007 15:56 To: SQL Subject: Update a table with a sub-query When I run this query: UPDATE tblCouncilEmailGroup SET MemberID = (SELECT Member_ID FROM tblCurrentCouncilMembers WHERE tblCouncilEmailGroup.DONEID = (tblCurrentCouncilMembers.DONE_ID)) I get the message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. I tried using the IN operator and still no go. Any ideas? Thanks, -- Bruce Sorge "I'm a mawg: half man, half dog. I'm my own best friend!" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2796 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
