Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: Operation must use an updateable query

    Hi! I wrote a query in SQL like this:

    Code:
    PARAMETERS param1 Long;
    UPDATE table1
    SET field1 = (SELECT COUNT(*) FROM table2)
    WHERE param1 = table1.field2;
    I get the error: Operation must use an updateable query. Isn't it possible to use a subquery in an update query. If I substitute it with a number, the update query works.
    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's a bit strange but Access refuses this syntax, though is is valid and accepted by SQL Server.

    If you are creating the query in Access, you can use:
    Code:
    PARAMETERS param1 Long;
    UPDATE table1
    SET field1 = DCount("<Any Column>", "Table2")
    WHERE param1 = table1.field2;
    By the way, the result would be the same, but personally I would write:
    Code:
    WHERE table1.field2 = param1;
    Have a nice day!

  3. #3
    Join Date
    Sep 2006
    Posts
    265
    An update in Access will not allow Aggregation Count Sum etc

    to get around this you need to:

    Set Table1.field1 = 0

    Then Set Table1.field1 = Table1.field1 + 1 and poll all the records and Access startes counts.

    Daff Yes.

    I haven't used this for Count only for Sum but the principle should be the same.

    Simon

  4. #4
    Join Date
    Sep 2009
    Posts
    16
    I solved in a different way. The update query was used to update a calculated field. I decided to solve by calculating it runtime instead of storing it. This because the solution of Sinndho was ok in this simple case, but my subquery was far more complicated and implied calculations. Thanks for your answers.

Posting Permissions

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