?
Solved

SQL to Update field from another table

Posted on 2013-01-21
11
Medium Priority
?
428 Views
Last Modified: 2013-01-22
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
Comment
Question by:cmccurdy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 1000 total points
ID: 38802096
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38802122
0
 

Author Comment

by:cmccurdy
ID: 38802197
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 35

Expert Comment

by:johnsone
ID: 38802237
What database are you using?
What version of the database?
What is your query?
What is the error you are getting?
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38802254
Is it something with ASP?

Found this in the MS Knowledge Base: http://support.microsoft.com/kb/175168
0
 

Author Comment

by:cmccurdy
ID: 38802820
Sorry,  I thought I had posted this in the Access area.

I'm using Access 2010
0
 

Author Comment

by:cmccurdy
ID: 38802935
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
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38803573
The error appears to be an Access error, which is outside my expertise - hopefully one of the Access experts will chime in.
0
 
LVL 10

Assisted Solution

by:Luke Chung
Luke Chung earned 1000 total points
ID: 38805791
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
 

Author Closing Comment

by:cmccurdy
ID: 38805940
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question