Update a table with a select

  • Thread starter Thread starter Warrio
  • Start date Start date
W

Warrio

Hello!

How is it possible to update a field with the value obtained from another
table (using only SQL coding without VBA)?
I've tried already 2 ways, both are not allowed by Access:

-1st way:-------------------------------------------
Update Table1 SET Field1 = (Select Field2 From)

-2nd way:------------------------------------------
Update Table1, (Select Field2 From) AS myTable2 SET Field1 =
myTable2.Field2
---------------------------------------------------

both of these queries get the same error:
-> Operation must use an updateable query

Thanks for any suggestion
 
Warrio said:
Hello!

How is it possible to update a field with the value obtained from
another table (using only SQL coding without VBA)?
I've tried already 2 ways, both are not allowed by Access:

-1st way:-------------------------------------------
Update Table1 SET Field1 = (Select Field2 From)

-2nd way:------------------------------------------
Update Table1, (Select Field2 From) AS myTable2 SET Field1 =
myTable2.Field2
---------------------------------------------------

both of these queries get the same error:
-> Operation must use an updateable query

Thanks for any suggestion
Click to expand...

Presumably your "Select Field2 From" is shorthand for what is really
going in there. But your second version works for me, so long as that
subquery not a totals query and doesn't use the DISTINCT keyword. Does
your subquery have a GROUP BY clause or use DISTINCT? Queries that use
those options become non-updatable.
 
Warrio said:
Thanks for you answer!
What if I have a group by statement into my subSelect ?
Click to expand...

I don't believe there is any way to get an updatable query if any part
of the query, even a subquery, uses the GROUP BY clause. In the case of
what you are trying to do, I'd say this is a shortcoming of the Jet
query engine, since it should still be possible to identify the specific
record to be updated -- that table is not being grouped. However, I
don't know any way to work around this problem with a single query. You
could run a three-step process: (1) turn the grouping subquery into a
make-table query and run it to create a temporary table, (2) execute the
update query using that temporary table in place of the subquery, and
(3) drop the temporary table.
 
Thanks for your help!!

Dirk Goldgar said:
I don't believe there is any way to get an updatable query if any part
of the query, even a subquery, uses the GROUP BY clause. In the case of
what you are trying to do, I'd say this is a shortcoming of the Jet
query engine, since it should still be possible to identify the specific
record to be updated -- that table is not being grouped. However, I
don't know any way to work around this problem with a single query. You
could run a three-step process: (1) turn the grouping subquery into a
make-table query and run it to create a temporary table, (2) execute the
update query using that temporary table in place of the subquery, and
(3) drop the temporary table.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top