Use an UPDATE on a SELECT in the same query-CodePudding

I currently use two queries in MS Access to insert data into my table OM_Extract.

Query 1:

INSERT INTO OM_Extract (cod, type, name, level)
SELECT table1.cod, table1.type, table2.name, table2.level
FROM table1 INNER JOIN table2 ON table1.cod = table2.cod;

Query 2:

UPDATE OM_Extract INNER JOIN table3 ON OM_Extract.level = table3.level SET OM_Extract.region = table3.region, OM_Extract.description = table3.description;

I'm fetching data from the final table (OM_Extract) in a VB application I'm using, but the way I'm doing it now, it forces me to always run an AutoExec macro in my Access so that my OM_Extract table gets updated.

I would like to create a single query that does not insert data into a table, so I can fetch the data directly from that query in my VB application.

It would be something like this:

UPDATE

SELECT table1.cod, table1.type, table2.name, table2.level AS OM_Extract
FROM table1 INNER JOIN table2 ON table1.cod = table2.cod

INNER JOIN table3 ON OM_Extract.level = table3.level SET OM_Extract.region = table3.region, OM_Extract.description = table3.description;

How can I do that?

CodePudding user response：

it looks like you are trying to extract your data out of a normalized table structure. In that case there is no need to update. If you want to update the data in table 2 then use a separate update query. So there is not even a need to refer to Table2.level or Table2.region and I leave them out of the example. Assuming:

'resulting sql for saved query OM_Extract
'either just call the saved function or duplicate the sql

SELECT Table1.cod, Table1.type, Table2.table2name AS name, Table2.level, Table3.region, Table3.description
FROM (Table1 INNER JOIN Table2 ON Table1.cod = Table2.cod) INNER JOIN Table3 ON Table2.level = Table3.level;

Notes: name is a reserved word in access. One way to handle that is to change name to something else in the access table and cast back to name later.

we have assumed no missing data by using INNER JOINS. Depending on the actual situation we might use other joins and or wrap the columns in the nz function