UPDATE SQL problem..


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: UPDATE SQL problem..

  1. #1
    Drew Curry Guest

    UPDATE SQL problem..

    I am trying to update field F1 in table T1 with the results from a
    summation query Q1. Q1 is most definitely not updateable. The update is
    simply a join from T1 to Q1 on the primary key with only F1 in the
    actual update.

    Access97 tells me the query must be updatable. Left-Joining T1 to Q1
    does not help. I normally simply wrap this in a trx in code, but am
    curious how to beat this seemingly simple operation.

    SQL (noting that the query Q1 is non-updatable):

    UPDATE T1
    LEFT JOIN Q1
    ON T1.PK = Q1.FK
    SET
    T1.F1 = IIf([Q1.F2=0,True,False);

    thanks!

  2. #2
    andy oneill Guest

    Re: UPDATE SQL problem..


    Either
    turn it into a make table query
    Must be to a local database as this will lock database entirely
    or
    Return the sum as a recordset.
    Build a bit of updateSQL or have a parameter update query and hand it the
    product


    Drew Curry <dcurry@no.spamola.home.com> wrote:
    >I am trying to update field F1 in table T1 with the results from a
    >summation query Q1. Q1 is most definitely not updateable. The update is


    >simply a join from T1 to Q1 on the primary key with only F1 in the
    >actual update.
    >
    >Access97 tells me the query must be updatable. Left-Joining T1 to Q1
    >does not help. I normally simply wrap this in a trx in code, but am
    >curious how to beat this seemingly simple operation.
    >
    >SQL (noting that the query Q1 is non-updatable):
    >
    >UPDATE T1
    >LEFT JOIN Q1
    >ON T1.PK = Q1.FK
    >SET
    >T1.F1 = IIf([Q1.F2=0,True,False);
    >
    >thanks!



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center