Update subquery

[Pettor]

Hello all,

I have the below update query, updating some data from table1 to table2 when the later are missing.
But some times some of these data are not stored in the table1 but in another table, table3.
So I would like to know if there is a way to run at first the first query and if it doesn't find any values, to look in the table3 for these data.
Probably if I run the same query but with the table3 instead of table1 could work and I may be able to do it with a subquery but I don't know exactly the way.

Can you please help? Thanks!

UPDATE table1 INNER JOIN table2

ON

(table1.CODE = [table2].CODE)

AND

(table1.MDATE = [table2].MDATE)

SET

[2table2].HOME = [table1].[HOME],

WHERE

([table2].HOME) Is Null);

 

[ranman256]

Q1, make an OUTER JOIN query t1 vs t2 to show whats NOT there.
then make another query using Q1 above and join to t3 to append the missing items.

 

[Pettor]

I am having a hard time with Outer Join and MS ACCESS, but could I do that with a UNION query? I have tried to do it but it doesn't seem to work either...

 

[Pettor]

Below is the query but I receive the message "Operation must use an updatable query. (Error 3073)


UPDATE

[2P_BET_TRACKING_UPDATE_UNION] INNER JOIN 2P_BET_TRACKING

ON

([2P_BET_TRACKING_UPDATE_UNION].

 = [2P_BET_TRACKING].[CODE])

AND

([2P_BET_TRACKING_UPDATE_UNION].[MDATE] = [2P_BET_TRACKING].[MDATE]) 
 
SET 
 
[2P_BET_TRACKING].HOME = [2P_BET_TRACKING_UPDATE_UNION].[HOME], 
[2P_BET_TRACKING].AWAY = [2P_BET_TRACKING_UPDATE_UNION].[AWAY], 
[2P_BET_TRACKING].LEAGUE = [2P_BET_TRACKING_UPDATE_UNION].[LEAGUE], 
[2P_BET_TRACKING].ODDS_1 = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_1], 
[2P_BET_TRACKING].ODDS_X = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_X], 
[2P_BET_TRACKING].ODDS_2 = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_2], 
[2P_BET_TRACKING].ODDS_U25 = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_U25], 
[2P_BET_TRACKING].ODDS_O25 = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_O25], 
[2P_BET_TRACKING].ODDS_GG = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_GG], 
[2P_BET_TRACKING].ODDS_NG = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_NG], 
[2P_BET_TRACKING].ODDS_0_1G = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_0_1], 
[2P_BET_TRACKING].ODDS_2_3G = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_2_3], 
[2P_BET_TRACKING].ODDS_4_6G = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_4_6], 
[2P_BET_TRACKING].ODDS_7PG = [2P_BET_TRACKING_UPDATE_UNION].[ODDS_7P]
 
 
WHERE 
 
((([2P_BET_TRACKING].HOME) Is Null)) 
OR ((([2P_BET_TRACKING].AWAY) Is Null)) 
OR ((([2P_BET_TRACKING].LEAGUE) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_1) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_X) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_2) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_U25) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_O25) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_GG) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_NG) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_0_1G) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_2_3G) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_4_6G) Is Null)) 
OR ((([2P_BET_TRACKING].ODDS_7PG) Is Null));

 

[BillyHagen]

I stopped by to thank you for the updated sub-query. Is there a way to run at first the first query? Doesn't it have any value?

 

[Pettor]

Now that I am seeing again the query I think the problem is that I am trying to update the union intead of the table3... So naive... Sorry but I am just a very new learner. I will test and revert.

 

[Pettor]

Can someone explain why I keep receiving this message "Operation must use an updatable query. (Error 3073)" from access? No matter How I tried to do this the destination table can not be updated...

 

[Pettor]

I just found another post with the same problem... It seems that access can not use update from the UNION query. I have first to create a table with the data and get them from there.