• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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
0
cmccurdy
Asked:
cmccurdy
2 Solutions
 
Steve WalesSenior 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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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 ChungPresidentCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now