Results 1 to 4 of 4

Thread: Update and subquery

  1. #1
    Join Date
    Mar 2005
    Posts
    2

    Angry Update and subquery

    I have created a query and I recieve an error stating that the operation must use an updatable query. The query I am trying to update is updatable and is very basic I only want to update a price field. The query to update is merely a mirror of the underlying table itself. The Price I want to set it to is a list from a subquery. I created a query (contains all price changes for 1 month then averages them) I base my subquery on this.

    My update SQL is as follows:
    UPDATE SwingSwap1 SET SwingSwap1.AvgPrice = (SELECT LocalUpdateGD.PRICE FROM LocalUpdateGD Where LocalUpdateGD.[DDATE]=SwingSwap1.DeliveryMonth);

    I have even tried:
    UPDATE SwingSwap1, LocalUpdateGD SET SwingSwap1.AvgPrice = [LocalUpdateGD].[PRICE]
    WHERE (([SwingSwap1].[DeliveryMonth]=[LocalUpdateGD].[DDate]));

    If anyone can help it would be appreciated. Thanks in advance.
    Dennis
    Last edited by DenBurt; 03-01-2005 at 01:28 PM.

  2. #2
    Join Date
    Mar 2005
    Posts
    2
    I think its strange but must be a quirk in Access (using Office XP). If I use a create table query to add a Temp table to contain the information in the [LocalUpdateGD] query then it is updatable and it works but this seems to be a step that isn't necccasary as the data in the [LocalUpdateGD] table is not being changed..... Does anyone know of a better way I can update these records, as to create and delete a table seems uneccesary?

    Thanks,
    Dennis

  3. #3
    Join Date
    Mar 2005
    Posts
    2
    Dennis -
    Access SQL can be weird - I find with subqueries (sometimes!) you have to use an AS statement. (Something like: FROM (subquery) AS [Subquery Alias].) I haven't used a subquery in an Update statement recently, so I can't remember if this is required there or not. In this case, however, I don't think you actually need a subquery anyway. Try the following SQL:

    UPDATE LocalUpdateGD RIGHT JOIN SwingSwap1 ON LocalUpdateGD.[DDATE]=SwingSwap1.DeliveryMonth SET SwingSwap1.AvgPrice = LocalUpdateGD.PRICE;

    By the way, why are you updating a field named "AvgPrice"? This name implies a calculated field on a view query, not a stored field on a table. Just curious.

    Hope this helps,

    Tyson

  4. #4
    Join Date
    Feb 2010
    Posts
    1

    Bug in Access

    In SQL*Server, this works:

    use jc1accts
    update customer
    set balance =
    (select sum(amount)
    from invoice
    where customer.c_no = invoice.c_no)

    In Access, you get this error:

    "Operation must use an updatable query"

    This clearly demonstrates a bug in Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •