Pls Help with Updating Access Table

[Guest]

I had asked for help on how to update two tables that are the same. I was
given the following:

UPDATE table1 AS T1 INNER JOIN table 2 As T2 on T1.id_column = T2.id_column
SET T1.col1 = Nz(T1.col1,T2.col1),T1.col2=Nz(T1.col2,T2.col2)

This worked as long as there was no data on the fields that were being
updated. But now I have to update to override existing data. Can I do this
in my query? How do I write that? Also if there is more than one column to
update, do I write this for each column? or can I write it all in one?
Thank you in advance for any help.

 

[Duane Hookom]

This should work. I would test it on a backup database prior to running it
where it would make a difference.

UPDATE table1 AS T1 INNER JOIN table 2 As T2 on T1.id_column = T2.id_column
SET T1.col1 = T2.col1,T1.col2=T2.col2

 

[Guest]

Thank you for your response. It is basically the same the only difference is
the "Nz" can you please explain "Nz". Do I write the same for each
field/column that needs to be updated? Or can I write this all in the ID
Column for all the fields? that need to be updated? Thank you once again for
all your help.

 

[Duane Hookom]

SET T1.col1 = Nz(T1.col1,T2.col1)
sets col1 of T1 equal to col1 of T2 if col1 of T1 is Null. If T1.col1 is
null then it will be updated to the value of T2.col1.

You can update as many columns as you like. Consider reading some
information on sql syntax before you make too many permanent changes to your
data.