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

    Question Unanswered: UPDATE multiple rows with values from another table

    Apologies if this is poor etiquette; I just crossposted this to the MS Access forum.

    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. In other words, this works fine:

    Code:
    UPDATE D
        SET D.[Total] = 0
        WHERE D.[Acct] 
            IN (SELECT [Acct] FROM D)
    Does anyone have any pointers or suggestions as to what I'm doing wrong?

    (Wild speculation, but could this have something to do with the type of cursor in play?)

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Re: UPDATE multiple rows with values from another table

    Code:
    UPDATE D
        SET D.Total = SUM(P.Amount) 
    FROM P, D 
    WHERE P.Acct = D.Acct 
    AND P.Type = 'PUR'
    GROUP BY D.Acct

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: UPDATE multiple rows with values from another table

    Originally posted by fadace
    Code:
    UPDATE D
        SET D.Total = SUM(P.Amount) 
    FROM P, D 
    WHERE P.Acct = D.Acct 
    AND P.Type = 'PUR'
    GROUP BY D.Acct
    Is that valid syntax in Access? It certainly isn't in Oracle.

    Also, I don't understand the following query in the original question:

    UPDATE D
    SET D.[Total] = 0
    WHERE D.[Acct]
    IN (SELECT [Acct] FROM D)

    What is the purpose of the WHERE clause? It is equivalent to saying:

    UPDATE D
    SET D.[Total] = 0
    WHERE D.[Acct] IS NOT NULL;

Posting Permissions

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