Update query

[tolson11]

I'm trying to create what I believe should be a simple update query, but I'm having some difficulties. What I'm trying to do is copy a value from one row in a table to another row in the same table. Here's an example of the syntax that I used...

UPDATE Table1 SET Field1 = (SELECT Field1 FROM Table1 WHERE Field2 = 1) WHERE Field2 = 2

Can anyone tell me what I'm doing wrong here? Thanks.

 

[macca the hacke]

Try

UPDATE [Table1] SET [Table1].[Field2]=[Field1];

This would change Field 2 to be the same as Field 1 in Table1

 

[tolson11]

I don't want to copy Field1 to Field2. I want to copy Field1 to Field1 for two different rows in the same table.

 

[nateobot]

I am not sure if this would be the 'right' way to do it, but it does work:

UPDATE Table1, Table1 AS Table1_1 SET Table1.Field1 = [table1_1].[field1] WHERE (Table1.Field2="2") AND (Table1_1.Field2="1")