SQL to Update field from another table

I need a query to update the LastPurchase date with the latest record for that vendor from another table.

Table1 - tbl_Vendors:
Fields - VendorID, LastPurchaseDate

Table2 - VendorID
Field - DateReceived
cmccurdyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
I believe this should handle it

update table1 a
set LastPurchaseDate = (select max(DateReceived) from table2 b where a.VendorId = b.VendorId)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
cmccurdyAuthor Commented:
sjwales - The query you gave errors with "Operation must use an updateable query"

angelIII - Thank you for the link, but my problem now is time, or the lack of, which is why I posted this as a question instead of researching why my query didn't work.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
johnsoneSenior Oracle DBACommented:
What database are you using?
What version of the database?
What is your query?
What is the error you are getting?
0
 
Steve WalesSenior Database AdministratorCommented:
Is it something with ASP?

Found this in the MS Knowledge Base: http://support.microsoft.com/kb/175168
0
 
cmccurdyAuthor Commented:
Sorry,  I thought I had posted this in the Access area.

I'm using Access 2010
0
 
cmccurdyAuthor Commented:
No, it's not related to ASP

I running Access 2010 and querying a local native database.

The query that gives the error is:

update tbl_Vendors a
set  LastPayment = (select max(DateReceived) from tbl_Parts b where b.VendorId = a.Id)
0
 
Steve WalesSenior Database AdministratorCommented:
The error appears to be an Access error, which is outside my expertise - hopefully one of the Access experts will chime in.
0
 
Luke ChungConnect With a Mentor PresidentCommented:
This has to do with a stupid limitation of Access queries that don't allow you to update data when an aggregation exists in the query.

Read my paper on Dealing with Non-Updateable Microsoft Access Queries and the Use of Temporary Tables
http://www.fmsinc.com/MicrosoftAccess/query/non-updateable/index.html

Hope that helps. It's part of our Microsoft Access Query Help Center: http://www.fmsinc.com/MicrosoftAccess/query/help-center.html
0
 
cmccurdyAuthor Commented:
Thanks everyone.  What I was trying to do with a single query apparently can't be done in Access.  I've split the points because sjwales's solution would have worked if it wasn't access and LukeChung-FMS showed me why it wouldn't work and gave the work arounds.<br /><br />I appreciate the help!
0
All Courses

From novice to tech pro — start learning today.