Update query problem

[Phil]

Access 2003
I have two tables which are related by a simple join. The second table
contains default values for one of the fields in the first table. I am
trying to produce an UPDATE query to set the first table back to the
defaults.
Here is what I have tried but I get an error message saying 'Operation must
use an updateable query'.

UPDATE Table1
SET Table1.Field1 =
(SELECT
Table2.DefaultField1Value
FROM
Table2
WHERE
Table2.ID=Table1.Table2_ID)

Any suggestions greatly appreciated
Thanks
Phil.

 

[bhicks11 via AccessMonster.com]

Hi Phil,

Are the tables joined on primary key or indexed fields? There are conditions
that make a query non-updateable and the join may be your problem.

Bonnie

http://www.dataplus-svc.com

 

[Phil]

Here is what I have tried but I get an error message saying 'Operation
must
use an updateable query'.

UPDATE Table1
SET Table1.Field1 =
(SELECT
Table2.DefaultField1Value
FROM
Table2
WHERE
Table2.ID=Table1.Table2_ID)

I think I was trynig to be over-complicated. This is much simpler, and has
the advantage that it actually works

UPDATE Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID
SET Table1.Field1 = DefaultField1Value;

I still don't understand why the first version didn't work though.

 

[Phil]

Are the tables joined on primary key or indexed fields? There are

conditions
that make a query non-updateable and the join may be your problem.

Thanks for your reply.

Yes the ID field in each table is a primary key, and there is a foreign key
relationship between them, and an index on the foreign key field. The help
says that you can't use a subquery with an aggregate function but I don't
have that.

I have found an alternative query to do the job now. See my other message.

Cheers,
Phil.

 

[John Spencer]

Because Access will realize that your subquery could (in theory) return more
than one row. So which row should it use to give you a value. Other
relational databases will work with the subquery and return an error if more
than one row is returned by the subquery.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Phil]

Because Access will realize that your subquery could (in theory) return

more than one row.

OK. That makes sense I suppose. Although I don't think it could ever return
more than one row if each record in Table2 contains a unique value for the
ID field.
In this case:
* It is an Autonumber field
* It is the primary key
* It is indexed with No duplicates

I think this guarantees uniqueness. Presumably Access is not clever enough
to check for this though.

Other relational databases will work with the subquery and return an error
if more than one row is returned by the subquery.

I'm sure I've used similar queries in the past, but perhaps it was when I
was using Oracle or SQL server.

Thanks,
Phil.