Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Washington, USA
    Posts
    7

    Unanswered: Updating all row in one table with values from another (crazy SQL)

    I want to update all rows in one table with values from another. The two tables have a one-to-many relationship (D[one]-----P[many]).

    As near as I can figure, the SQL needs to look like:
    Code:
    UPDATE D
        SET D.[Total] = 
            (SELECT SUM(Amount) 
                FROM P 
                WHERE P.[Acct] = D.[Acct] 
                    AND P.[Type] = 'PUR')
        WHERE D.[Acct] 
            IN (SELECT [Acct] FROM D)
    Unfortunately, I'm trying to do this in an MS Jet DB (through Access 2000 & ADO 2.5), so I'm not sure if my SQL is wrong-headed, or if the application simply won't do what I'm trying to tell it.

    As the above stands, I get an error: "Operation must use an updateable query." The help topic associated with the error suggests that the query may be attempting to update the one side of a one-to-many relationship (it is). However, if I substitute a static value for the SET subquery (say, 0), it works fine, but obviously not as intended.

    Does anyone have any pointers or suggestions as to what I'm doing wrong?

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: Updating all row in one table with values from another (crazy SQL)

    the join in your subquery is not there, for you have to declare both tables. try fixing the join and see what it does like below
    Regards
    Jim
    UPDATE D
    SET D.[Total] =
    (SELECT SUM(Amount)
    FROM [table A] P , [Table B] d
    WHERE P.[Acct] = D.[Acct]
    AND P.[Type] = 'PUR')
    WHERE D.[Acct]
    IN (SELECT [Acct] FROM D)

  3. #3
    Join Date
    Oct 2002
    Location
    Washington, USA
    Posts
    7

    Still not working

    Thanks for the reply. Unfortunately, that didn't get me much further. I tried:
    Code:
    UPDATE D
        SET D.[Total] = 
            (SELECT SUM(Amount) 
                FROM P INNER JOIN D
                    ON P.[Acct] = D.[Acct] 
                WHERE P.[Type] = 'PUR')
        WHERE D.[Acct] IN 
            (SELECT [Acct] FROM D)
    and also:
    Code:
    UPDATE D
        SET D.[Total] = 
            (SELECT Amount FROM P, D
                WHERE P.[Acct] = D.[Acct] 
                    AND P.[Type] = 'PUR')
        WHERE D.[Acct] IN 
            (SELECT [Acct] FROM D)
    But I keep hitting the same error: 'Operation must use an updateable query.' I see that many people hit this error from misconfigured permissions; that is not the case here - I can make this work by substituting a static value for the subquery, as I mentioned.

    Also, eschewing the aggregate function in the SET subquery doesn't work either. Access has built in 'Domain Aggregate' functions, which I'm currently investigating, but I'm not confident they're going to help.

Posting Permissions

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