Update Query with Blank Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to update a table with updates that are in another table. For
example, one record in the updates table might have a person's new address,
another might have a new last name or corrected date of hire, etc. Most of
the fields for a record in the update table will be blank, however. That is,
for an address change, you would just have the person's social security
number and address information, but their last name, date of hire, etc. would
be blank.

My question is, can I create a single query that will apply these updates to
the other table, but without "updating" the fields that are blank for that
record in the updates table. For example, if someone in the updates table has
a corrected address, but his date of hire and name are blank, I want to
update his address info in the original table, but I want to leave the date
of hire and name as is, not replace them with blanks. Can this be done
without spliting up the updates into different tables?

Thanks in advance for any help you can provide.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

UPDATE Table1 As T1 INNER JOIN Table2 As T2
ON T1.id_column = T2.id_column
SET T1.colA = Nz(T2.colA,T1.colA),
T2.colB = Nz(T2.colB,T1.colB),
.... etc. for all other columns ...

The Nz() function will reinsert the existing value in Table1's column
when the column in Table2 is NULL. You must make sure the column values
in Table2 are NULL and not empty strings. Unfortunately, the default
setting of Allow Zero Length Strings is set to Yes in the new Access
tables (Acc2002 & >).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAkEkIechKqOuFEgEQKrtgCgw9gHYiurVQzCizamUOPeeKWFl6sAn0HL
qBN5aU1pG56AvvyZQ6Eudkxq
=TATu
-----END PGP SIGNATURE-----
 
Back
Top