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

x
?
Solved

Append or Update Query

Posted on 2014-10-04
3
Medium Priority
?
183 Views
Last Modified: 2014-10-05
I want to run a query that updates a record I a table if the record already exists.   But if the record does not exist I want it to create a new record in the table.

The source table is "Source".  The destination table is "Destination"

The both have a key field named "RecordID"

If I just try to run an update query it fails because the record isn't there.  But if I run an append query it works fine if the record is already there.

Confused!!

--Steve
0
Comment
Question by:SteveL13
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 40361418
this can be done by using a Left join instead of an Inner join in your update query


update source LEFT JOIN destination
ON source.recordid = destination.recordid
SET destination.[field1] = [source].[field1], destination.[field2] = [source].[field2], destination.[field3] = [source].[field3];
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40361494
What Rey said!  (no points please)

Some people call this an UPSERT query.  In SQL Server, they have a similar query syntax called MERGE which gives you the ability to Update and Append at the same time, and you can even get a count of how many of each action are completed.
0
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1000 total points
ID: 40361573
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

963 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