Update with Select statement in SET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am trying to understand why Access cannot run this statement. I have a
SET statement with a Select Statement. Access keeps telling me that the
operation must be an updateable query. I know that there is nothing wrong
with the Update statement because I can replace the Select statement wtih
some static data and it works fine. The Select statement is coming from
another query, and I know it runs fine.

UPDATE TempHedgeDoc
INNER JOIN dbo_deals ON TempHedgeDoc.Quantum = dbo_deals.deal_no
SET TempHedgeDoc.firstNextText =
(SELECT Top 1 dbo_deals.deal_no FROM dbo_deals INNER JOIN TempHedgeDoc
ON (dbo_deals.ccy2 = TempHedgeDoc.ccy2) AND (dbo_deals.ccy =
TempHedgeDoc.ccy) AND (dbo_deals.mature_dt = TempHedgeDoc.mature_dt) WHERE
dbo_deals.trans_type="fx" GROUP By deal_no, dbo_deals.deal_dt ORDER BY
dbo_deals.deal_dt, dbo_deals.deal_no);
 
Unfortunately, Access decides that Select TOP 1 could possibly return more
than one value due to ties. Note, it doesn't run the query and generate an
error because more than one record is returned, it just realizes there is a
potential for this to happen.

The only way to use this in an update is to dump the necessary information
to a table (temporary) and then use the table in your update query OR create
a vba function to return the needed value.

HMMM. I wonder if Duane Hookom's concatenate function could be modified to
return the needed value and if so would Access allow it to work in an update
query?
 
Hello,

I am trying to understand why Access cannot run this statement. I have a
SET statement with a Select Statement. Access keeps telling me that the
operation must be an updateable query. I know that there is nothing wrong
with the Update statement because I can replace the Select statement wtih
some static data and it works fine. The Select statement is coming from
another query, and I know it runs fine.

UPDATE TempHedgeDoc
INNER JOIN dbo_deals ON TempHedgeDoc.Quantum = dbo_deals.deal_no
SET TempHedgeDoc.firstNextText =
(SELECT Top 1 dbo_deals.deal_no FROM dbo_deals INNER JOIN TempHedgeDoc
ON (dbo_deals.ccy2 = TempHedgeDoc.ccy2) AND (dbo_deals.ccy =
TempHedgeDoc.ccy) AND (dbo_deals.mature_dt = TempHedgeDoc.mature_dt) WHERE
dbo_deals.trans_type="fx" GROUP By deal_no, dbo_deals.deal_dt ORDER BY
dbo_deals.deal_dt, dbo_deals.deal_no);
Click to expand...

It's an annoying limitation of the JET database engine. Any Totals
query, or any query *including* a Totals query - even one like this
which should logically be updateable - is ipso facto non-updateable.

You may need to make your subquery into a MakeTable query into a
temporary table, and run your update query based on that.

John W. Vinson[MVP]
 
Thank you both for your responses...I will have to check about a possible
work around and get back to you.
 
Back
Top