If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update query problem
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. |
#2
|
|||
|
|||
Update query problem
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 wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200806/1 |
#3
|
|||
|
|||
Update query problem
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. |
#4
|
|||
|
|||
Update query problem
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. |
#5
|
|||
|
|||
Update query problem
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 wrote: 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. |
#6
|
|||
|
|||
Update query problem
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. |
Thread Tools | |
Display Modes | |
|
|