Error Message in Update Query.

[ofilha]

I am getting the following error: "Operation must use an updatable query"
I am trying to update a table that has no keys. I need to update one field in one row based on three fields in another row. To make it easier i copied the rows i need to compare to another table called newtable. Then i created the following query.

update table1 set field1 = ( select  newtable.acom from newtable, table1
where ( newtable.somecode="TCP"+mid(newtable.somecode,4,15) and table1.field2=newtable.field2)
 
)
where table1.field1= "TC"+mid(table1.field1,3,15);

But i keep getting the error message above. I have not been able to figure out a way to update table1. Any suggestions as to what i am doing wrong?
Thanks

[NeoPa]

Let's tidy up the code a little and see where that gets us first.

UPDATE [Table1]
SET [Field1]=(SELECT [Acom]
              FROM [NewTable] AS NT, Table1 AS T1
              WHERE NT.SomeCode Like 'TCP*'
                AND NT.Field2=T1.Field2)
WHERE [Field1] Like 'TC*';

This may not fix the whole problem but it's likely to be a much clearer position from which to work.
Also, your subquery uses a non-joined pair of tables. Is this what you intended?

[ofilha]

Let's tidy up the code a little and see where that gets us first.

UPDATE [Table1]
SET [Field1]=(SELECT [Acom]
              FROM [NewTable] AS NT, Table1 AS T1
              WHERE NT.SomeCode Like 'TCP*'
                AND NT.Field2=T1.Field2)
WHERE [Field1] Like 'TC*';

This may not fix the whole problem but it's likely to be a much clearer position from which to work.
Also, your subquery uses a non-joined pair of tables. Is this what you intended?

I will give it a try. Regarding your question, i am not sure. I actually need to perform the operation on one table. I merely put the records i wanted to get the data from in another table. But what i want is to update one column in one row from another column in another row based on the content of three rows which either match or partially match each other. I hope this makes sense. The table i am working on is not indexed and has no keys.

[NeoPa]

You will need to provide a clearer explanation if you want help I'm afraid.
Table MetaData would be required to show us the sort of thing you're working with. Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time