Update Query - Simple

[mmedia]

Hi everyone
I need to know how I can update a field in table 1 and set it to =1 after matching an id in table 1, that should be the same as the ID in table 2. Thats it. I can't seem to get this to happen.

something like:

Update table1
set fieldname_5 = 1
where table1.field1 = table2.field1

does this make sense,? I am using sql in access and need to know asap thanks ppl.

 

[SQL_Hell]

Hi mate,

Close, but use a sub query like this

Update table1
set fieldname_5 = 1
where table1.field1 IN (SELECT field1 FROM table2)

 

[mmedia]

To check

When I use this:

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

it gives me:
operation must use and updateable query

I will try your suggestion. thanks

 

[mmedia]

No joy

Well it seems to work, but it didnt update any records. I am now just using two very simple tables. Table 1 has

ID, Field1, Field2

1, Pete, Robinson
2, Sam, Walker
3, Joe, Hindes


Table 2

ID, Field1, Field2
1,
2,
3,

I used what you said

Update table2
set field1 = 1
where table1.field1 IN (SELECT field1 FROM table2)

also what would I write if I wanted for it to pickup the names in table1 and update them into table2 etc?
thanks for your help

 

[SQL_Hell]

Here is another way of doing it, sorry I'm a bit out of practice with the Access querkyness.

Here is a method i use in SQL server, it should work in JET SQL

UPDATE table1
SET Surname = c.Surname
FROM table t
INNER JOIN table2 c ON c.ID = t.ID

 

[mmedia]

Its working

Thanks ive got it to work now, I need one more query. I need to update the main table after making a comparison of an ID...so

Insert ID in table1 if
ID is not in table1 but is in table2.

Thanks again for all your help