Append Query for Records That Already Exist

[Tor_Fey]

Good Afternoon all;

I have two test tables I’m trying to perform an experiment on, test1 & test2.
In both these tables; I have a field called ‘yearis’ which is a text field. What I’d like to do is create an append query that will: Update records in table 2 if it exists or append a record from table 1 if it does not exist. According to the ‘yearis’ field in test1.

Is there a way to do this with a standard append query?

Many Thanks for your help.

Kind Regards
Tor Fey

 

[Minty]

Short answer no. You need two queries, an append for the new data, and an update for existing.

They are created differently in SQL, and perform different actions, so can't be combined.

 

[JHB]

..
Is there a way to do this with a standard append query?
...

The technique is described in the link by Gustav Brock, a little down the page.
https://www.experts-exchange.com/questions/23594743/Combined-update-or-Append-query.html

 

[Minty]

Now why didn't I think of that...

 

[isladogs]

Clever.
Is there a recognised name to describe this method which does both APPEND & UPDATE in one query.

UPAPP? UPEND? APPDATE? APPUP?
I like UPEND

 

[Minty]

Apparently it's called an UpSert, but that's probably a bit too close grammatically in this day and age, to an UpSkirt , which is an entirely different and NSFW activity!

 

[isladogs]

Ah yes - I remember Upsert. Thought I'd seen it before.
As far as I'm concerned, UPEND is a much better name

 

[Pat Hartman]

I'm pretty sure this method only works with Jet/ACE. I don't believe it works in SQL Server, just FYI.