How to make an UPSERT query using another Query as the source

[raziel3]

I was reading this thread

Append if rows do not exists else update if exists

Ok Guys i have one more question. I would like to create new recordset from t_AnimalInput (UPSERT table but with condition). So in one sql to do: 1. Check if Index equals 1 or 2 and take only these rows to create upsert query 2. Check if rows exist in t_AnimalSource and append if not 3. If...

www.access-programmers.co.uk

and was wondering how to do an UPSERT using a query (not a table) as the source. I am trying the method described here and I am getting a "Operation must use an updateable query" error

UPDATE AVGITEMCOST LEFT JOIN tblITEMTEST ON AVGITEMCOST.PIN = tblITEMTEST.PRICEID
SET tblITEMTEST.PRICEID = [AVGITEMCOST].[PIN];

AVGITEMCOST is the source query.

 

[isladogs]

You could try UPDATE DISTINCTROW.... To fix the updateable query error
That's equivalent to setting Unique Records = True in the property sheet
However it may only give one appended record..
See my article https://www.isladogs.co.uk/upend-query/index.html

Why not just use the table instead?

 

[Pat Hartman]

The "upsert" query is an Access SQL construct. It doesn't work in SQL Server and it isn't logical that it would work with a query. I believe it also uses a right join.

 

[raziel3]

I was trying to avoid the step to "Make a Table". The query AVGITEMCOST is a distinct query, initially I used it to make the Items table but now as new and unique Items are coming into the db, I wanted to used that query to update and/or add the new items to the main table.

So what you guys are saying is that a Table have to be made for UPEND/UPSERT to work?

What about doing it through VBA?

 

[Pat Hartman]

NO. You are inserting or Adding to an EXISTING table whether you use two queries or one.

Queries don't store data. Tables store data.

Did you try changing the query to a right join so that the table you are updating comes first?

 

[raziel3]

PIN is the new items from AVGITEMCOST query
PRICEID is the existing ITEMS Table.
So 5 new items have been detected DECKING 18G, DECKING 20G.....

This is the query I'm using. Same error.

UPDATE AVGITEMCOST LEFT JOIN tblITEMTEST ON AVGITEMCOST.PIN = tblITEMTEST.PRICEID SET tblITEMTEST.PRICEID = [AVGITEMCOST].[PIN];

 

[isladogs]

Can you upload a cut down version of the database so we can look at the issue.
We only need the relevant tables and queries with enough data for testing.
Change or omit any confidential data

 

[sonic8]

The "upsert" query is an Access SQL construct. It doesn't work in SQL Server [...]

"UPSERT" is a generic term for a SQL operation combining INSERT and UPDATE. How it can be implemented/written differs by the DBMS at hand.
With Microsoft SQL Server (and several other DBMS) you can implement it using the MERGE statement.
With MySql it can be done simply and intuitively with the INSERT ... ON DUPLICATE KEY clause.

Access, once again, has the poorest support for UPSERT of them all. In most scenarios you must run two consecutive UPDATE and INSERT queries to achieve it. Only in very few scenarios it is possible to use an Update statement on an Outer Join query to achieve an UPSERT. - I consider this approach so "hacky" that I wouldn't even use it, if it were possible for a certain task.

 

[raziel3]

As requested.
Query1 is the UPSERT query I was working on. As @sonic8 was saying, I was thinking to forget this method. It's not too elegant.
But I would like to see a solution before going to VBA.

 

[isladogs]

Query1 is read only because the underlying query AVGITEMCOST that it uses is read only
If you can modify that so it is editable, your UPSERT query might work.

If not, then just use two separate queries, INSERT and UPDATE
No need for VBA

 

[Pat Hartman]

The Upsert query is quite "elegant" when you implement it correctly.

You are never going to be able to be able to make this an upsert query since part of it uses a union and union queries are not updateable. Access is pretty persnickety on this point. It doesn't matter whether or not you are trying to update the non-updateable part of the query. If ANY part of the query is not updateable, then NONE of the query is updateable.

Given that the source of the PIN is the union query, you are probably going to need six queries 3 update and 3 append to handle each of the separate tables in the union query. OR, you could think about normalizing the schema.

 

[raziel3]

If ANY part of the query is not updateable, then NONE of the query is updateable.

Ah, I see. Ok, understood.

It will be impossible to get AVGITEMCOST editable, well because you've seen the design. Because of the variations of Items produced, AVGITEMCOST is being used to categorize it. Then again, thinking about it, I can put all the tables into one and eliminate the union query, but AVGITEMCOST is a distinct query so it still won't be updateable.

 

[NauticalGent]

Access, once again, has the poorest support for UPSERT of them all. In most scenarios you must run two consecutive UPDATE and INSERT queries to achieve it. Only in very few scenarios it is possible to use an Update statement on an Outer Join query to achieve an UPSERT. - I consider this approach so "hacky" that I wouldn't even use it, if it were possible for a certain task.

 

[Pat Hartman]

I would disagree on "must". You only have a problem when ONE part of the query is not updateable and that generally arises only in a poorly designed schema where you are aggregating data.