UPDATE xx SET xx = (SELECT subquery) -- not updateable

  • Thread starter Thread starter Dick Watson
  • Start date Start date
D

Dick Watson

I was just reading the archives and find the "Solution" to this problem is a
Dlookup domain function instead of the subquery. I can use this, but surely
it's suboptimal and has performance impact.

Why doesn't Jet allow this? Why shouldn't it just be content to solve the
subquery and then use that value for the UPDATE? Is this limitation
documented somewhere? Is there a better workaround that the Dlookup? It
seems like using a subquery to do this would be a common design solution...
 
Hi,


UPDATE table1 INNER JOIN table2 ON table1.f1=table2.f2
SET table1.f3=table4.f4


could replace

UPDATE table1 SET table1.f3=DLookup("f4", "table2", "f2=" & f1 )



Hoping it may help,
Vanderghast, Access MVP
 
Dick Watson said:
I was just reading the archives and find the "Solution" to this problem is
a Dlookup domain function instead of the subquery. I can use this, but
surely it's suboptimal and has performance impact.

Why doesn't Jet allow this? Why shouldn't it just be content to solve the
subquery and then use that value for the UPDATE? Is this limitation
documented somewhere? Is there a better workaround that the Dlookup? It
seems like using a subquery to do this would be a common design
solution...
Click to expand...
Hi Dick,

Access SQL DOES NOT support subqueries in SET clause
(ANSI SQL does)

Access SQL DOES support joins in UPDATE clause
(nonstandard SQL)

some examples:

/////////////////////////////////////////
update multiple fields in main table
based on values in identical fields
from a different table


UPDATE table1 INNER JOIN table2
ON
table1.primarykeyfield = table2.foreignkeyfield
SET table1.field1 = table2.field1,
table1.field2 = table2.field2

////////////////////////////////////////

Example from ADH
Change phone number in tblC to new phone number in tblM

UPDATE tblM INNER JOIN tblC
ON tblM.ID = tblC.ID
SET
tblC.PhoneNumber = tblM.PhoneNumber
WHERE tblC.PhoneNumber<>tblM.PhoneNumber;

////////////////////////////////

UPDATE TempEquityAdjustments LEFT JOIN InvestmentsTable ON
(TempEquityAdjustments.InvestMgrID = InvestmentsTable.InvestMgrID) AND
(TempEquityAdjustments.CurrentDate = InvestmentsTable.CurrentDate) AND
(TempEquityAdjustments.ClientID = InvestmentsTable.ClientID)
SET
[InvestmentsTable].[Equity] = TempEquityAdjustments.Equity,
[InvestmentsTable].[CURRPROF] = TempEquityAdjustments.Profit;

////////////////////////////

If your "subquery" is not an aggregate query
and has field(s) that link back to update table,
then one typical method is to save the subquery
and join in your update query.

good luck,

gary
 
The complication with the JOIN methods is that there is nothing to join
between the two tables in question. What I'm really doing (with syntax that
gags Access SQL):

UPDATE t1 SET t1.fkeyptr = (
SELECT t2.fkeyvalue FROM t2 WHERE (t2.f2 = "a value known to be there");
) WHERE t1.f2 IN (
SELECT DISTINCT t3.f1 FROM t3 WHERE (t3.f1 Is Not Null);
);

T1 is account information including a foreign key of a default currency to
T2 which is valid currencies. T3 is a table of imported account transactions
known to be in the "known value" currency and used to identify the accounts
that need to be set to that currency. This last fact is just a known
characteristic of the data; it's not something in the data that the engine
can use to join.

I suppose I could store yet another intermediate query and do it like this:

q1.SQL=
SELECT DISTINCT t3.f1, (
SELECT t2.fkeyvalue FROM t2 WHERE (t2.f2 = "a value known to be there");
)
FROM t3 WHERE (t3.f1 Is Not Null);

then my query becomes:

UPDATE t1 INNER JOIN q1 ON t1.f2 = q1.f1 SET t1.fkeyptr = q1.fkeyvalue;

(At least I'm assuming this will work based on the replies in the thread. I
can't readily test it here.)

I'm just trying to avoid storing all of these intermediate queries since
they seem so fragile--I forget where they are used and "fix" them or delete
them only to discover later that they are used in some other query.

Thanks for the help!
 
Hi,


The sub select query is prone to produce an error.


SELECT t2.fkeyvalue FROM t2 WHERE (t2.f2 = "a value known to be there")


if t2.f2 is not a potential key, then that select MAY return more than one
record,

.... SET t1.f1 = manyRecords

is thus an error, under such circumstances.



UPDATE t1, t2
SET t1.fkeyptr=t2.fkeyvalue
WHERE t2.f2="know value"
AND t1.f1 IN( SELECT f3 FROM t3 WHERE t3 IS NOT NULL)



would work (should work) even in that case, "remembering" only the last
updated value.


Sure, there is nothing that hints the optimizer that there is just ONE
record in t2 satisfying the WHERE criteria, which is probably the case, so
that formulation may be much less efficient, in the end, than the DLookup
approach.


A most efficient way could be to use a virtual table:


UPDATE t1, (SELECT fkeyvalue FROM t2 WHERE t2.f2="known Value") As x
SET t1.fkeyptr=x.fkeyvalue
WHERE t1 IN (SELECT ... )


where the sub query is clearly un-correlated, and "scalar", and easily
picked up as such, by the optimizer.


There is no need to add DISTINCT in the IN( ) clause, at least, none I see.


Hoping it may help,
Vanderghast, Access MVP
 
Comments inline.

Michel Walsh said:
The sub select query is prone to produce an error.

SELECT t2.fkeyvalue FROM t2 WHERE (t2.f2 = "a value known to be there")

if t2.f2 is not a potential key, then that select MAY return more than one
record,

... SET t1.f1 = manyRecords

is thus an error, under such circumstances.
Click to expand...

It's key--though not designated as such--but it is indexed no duplicates.
UPDATE t1, t2
SET t1.fkeyptr=t2.fkeyvalue
WHERE t2.f2="know value"
AND t1.f1 IN( SELECT f3 FROM t3 WHERE t3 IS NOT NULL)

would work (should work) even in that case, "remembering" only the last
updated value.

Sure, there is nothing that hints the optimizer that there is just ONE
record in t2 satisfying the WHERE criteria, which is probably the case,
so that formulation may be much less efficient, in the end, than the
DLookup approach.

A most efficient way could be to use a virtual table:

UPDATE t1, (SELECT fkeyvalue FROM t2 WHERE t2.f2="known Value") As x
SET t1.fkeyptr=x.fkeyvalue
WHERE t1 IN (SELECT ... )

where the sub query is clearly un-correlated, and "scalar", and easily
picked up as such, by the optimizer.
Click to expand...

It took me a while to get this setup--basically getting the SQL right since
there is no way to create this in design view, but this does seem to work
quite well. Thanks. I think this is the one I'll stick with.
There is no need to add DISTINCT in the IN( ) clause, at least, none I
see.
Click to expand...

I did this because the query can return many hundreds of each of several
account names. If Money is smart enough to use these in the IN clause as
though they were DISTINCT, then I will omit it. My original thought was it
might try hundreds of useless compares.
 
Back
Top