Re: [AccessDevelopers] Update query problems

[Toby Bierly]

Title: Update query problems

You cannot use joins in an update query.  The query won't know which table to update, among other reasons.   I think you can accomplish what you want with a subquery.  For example, something like this (you may have to play a little with the syntax -- I haven't tested it):   UPDATE tbl_CustomerHistory SET tbl_CustomerHistory.Rank = 0 WHERE (SELECT qry_CustomersWithHighestAgeRank.Customer             FROM tbl_CustomerHistory             LEFT JOIN qry_CustomersWithHighestAgeRank             ON tbl_CustomerHistory.Customer = qry_CustomersWithHighestAgeRank.Customer) Is Null;   Also, I believe that the subquery must return only 1 row.   HTH, Toby ----- Original Message ----- From: David Pearson To: AccessDevelopers@yahoogroups.com Sent: Tuesday, June 28, 2005 11:56 PM Subject: [AccessDevelopers] Update query problems Hello all, I have table that contains a list of customers with various details (one of with is a rank). I have created an update query and linked the customer code from my table to the customer code in a query and the theory is that if the customer field in the query is null then it is not in there so update the rank in the table to 0.  Here is the SQL UPDATE tbl_CustomerHistory LEFT JOIN qry_CustomersWithHighestAgeRank ON tbl_CustomerHistory.Customer = qry_CustomersWithHighestAgeRank.Customer SET tbl_CustomerHistory.Rank = 0 WHERE (((qry_CustomersWithHighestAgeRank.Customer) Is Null)); This is really annoying me now because when I try and run the query it tells me that "Operation must use an updateable query." Any help would be appreciated. Regards David Pearson IT Support Manager Sidhil Ltd. Sidhil Business Park Holmfield Halifax HX2 9TN T: +44 (0) 1422 233062 F: +44 (0) 1422 233040 E: [EMAIL PROTECTED] www.sidhil.com ________________________________________________________________________ This e-mail has been scanned for all viruses by Star. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ Please zip all files prior to uploading to Files section. ============================================================= The information in this Internet email is confidential and intended for the sole use of the addressee(s). Copying, disclosure or re-use, in any way, of the information contained in this email by anyone other than the addresse(s) is unauthorised. We accept no legal responsibility for the content of this message. Any opinions or views presented are solely the responsibility of the author and do not necessarily represent those of Siddall and Hilton Ltd. and any of their subsidiary companies. If you have read this email in error, please return it to the sender and remove from your system. Siddall and Hilton Ltd. and any of their subsidiary companies may monitor the content of emails sent and received via its network for the purposes of ensuring its compliance with its policies and procedures. ============================================================= Please zip all files prior to uploading to Files section. YAHOO! GROUPS LINKS  Visit your group "AccessDevelopers" on the web.    To unsubscribe from this group, send an email to:  [EMAIL PROTECTED]    Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.