Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • 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 free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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