Update union query (1 Viewer)

[jaryszek]

Hi,

i have union query like here:

SELECT * from Temp_FEversion
UNION ALL SELECT * from Temp_Hanaversion
UNION ALL SELECT * from Temp_NWversion;

How can i update Function field from these tables?

I am getting :

Please help,
Jacek

 

[theDBguy]

Hi. One way is to create a temp table from your union query and then use the temp table for the update query.


Edit: After re-reading your question/post, I am thinking the above approach may not work either. You may just have to execute multiple UPDATE queries, one for each table, to update your "function" field in each one. Sorry...

 

[The_Doc_Man]

According to Allen Browne, whom we consider to be a REALLY GOOD reference for such things, UNION queries cannot be updated.

http://allenbrowne.com/ser-61.html

You can use either .Recordset operations or make an SQL string dynamically to perform a single-table update via CurrentDB.Execute or DoCmd.RunSQL on your SQL string.

 

[jaryszek]

Thank you Guys.

Oo so this seems to be a problem.

Jacek

 

[The_Doc_Man]

Yep, UNION queries are great for what they do, but Access has issues with updates of any kind, probably because of difficulties in finding the targeted record for the writeback. When you generated the UNION query, you REALLY generated a single list of records taken from all contributing SELECT queries. But that list (to the best of my understanding) is actually just a list of record file addresses (i.e. where ON THE DISK to find the records). It does not (again, to the best of my understanding) include the source table for each record, so the update function (and, for that matter, DELETE) will not know which table to update. And the append query has the same issue - more than one possible table. It is the ambiguity of location that makes the problem difficult.

 

[jaryszek]

thank you The_Doc_Man - you know that you have talent to explanation in very simple english difficult things?

Jacek

 

[The_Doc_Man]

I can only try. I wish you could convince my wife of that when I am trying to explain to her why her printer won't print what she wants to print.