Query that update a field from another table

[jbranero]

Hi there, I have a table with some missing values in a field. I got another table with the values I need.
I tryed to make a an append Query for update the values but this query add new record and I don't need that.
Any idea over there.
thanks

 

[earthandfire]

Append queries will add new records. You need to look at Update queries.

 

[Trevil]

You will need to have a field or fields in each table that will allow you to match the records you want to update.

The following is an example where 'fld1' is the key and you want to update fld3:
UPDATE tbl1 INNER JOIN tbl2 ON tbl1.fld1 = tbl2.fld1 SET tbl2.fld3 = [tbl1]![fld3];


Code: Where the vision is often rudely introduced to reality!

 

[jbranero]

I try with the update query and the SQL look good but for some reason it doesn't update the field
The SQL is:

UPDATE T1 INNER JOIN T2 ON T1.ID = T2.ID SET T1.CUSTNUM = [T2]![CUSTNUM]
WHERE (((T1.CUSTNUM)="IsNull"));

What I try is to update the CUSTNUM in those records where is empty.
I click on Datasheet View and all what I see is the former values, that means nothing

Any help?

 

[RoyVidar]

That's looking for a field containing the text "IsNull", try something like this in stead

UPDATE T1 INNER JOIN T2 ON T1.ID = T2.ID SET T1.CUSTNUM = [T2]![CUSTNUM]
WHERE T1.CUSTNUM Is Null;

Roy-Vidar

 

[jbranero]

I did it already. In fact I added some stupid value to some records in the fields ("a") to check what was wrong, but nothing happen, I mean do not update.
It looks like the Query works because it shows the records that meet the condition Is Null but they are not updated.

 

[RoyVidar]

You know the difference between switching views with the leftmost button in the query view and executing a query through the "!" button?

Are there any matches here, what happens if you do a

[tt]select T1.CUSTNUM, T2.CUSTNUM
from T1 INNER JOIN T2 ON T1.ID = T2.ID
WHERE T1.CUSTNUM Is Null;[/tt]

Roy-Vidar