access 2007: sql update query return some error

Posted on 2012-08-12
Last Modified: 2012-08-27
I have a query below in my access

UPDATE invoice SET invoice.[nett profit] = (select [amount]-[supplier cost] from invoice as profit);

when I execute this query it return me with error " operation must use an updateable query"

anyone know this ? and how to fix this error
Question by:motioneye
    LVL 119

    Assisted Solution

    by:Rey Obrero
    try this

    UPDATE invoice SET invoice.[nett profit] =   [amount]-[supplier cost]


    UPDATE invoice SET invoice.[nett profit] =   invoice.[amount]-invoice.[supplier cost]
    LVL 48

    Accepted Solution

    First, (select [amount]-[supplier cost] from invoice as profit) can return more than one record.
    Second, you are updating a table with data from itself but other records.

    Write the output (select [amount]-[supplier cost] from invoice as profit) to a temp table and update from that - or rewrite (select [amount]-[supplier cost] from invoice as profit) to return one value only.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now