We help IT Professionals succeed at work.

access 2007: sql update query return some error

motioneye
motioneye asked
on
Hi,
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
try this


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

or

UPDATE invoice SET invoice.[nett profit] =   invoice.[amount]-invoice.[supplier cost]
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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.

/gustav

Explore More ContentExplore courses, solutions, and other research materials related to this topic.