Update table using data from another 1

[LenRI]

I am trying to conditionally populate a field in Table2 by using data from Table1. Essentially both tables have fields A, B and C (i know this is a horrible design practice, but its not up to me). What I am trying to accomplish here is to populate field C in Table2 with that of a corresponding value in Table 1 in only in those instances where Table1.A=Table2.A AND Table1.B=Table2.B


Here's the table structure just in case:

Table1
=======
A
B
C


Table2
=======
A
B
C

 

[swampBoogie]

update table2 inner join table1 
on (table1.a = table2.a and table1.b = table2.b)
set table2.c = table1.c

 

[Tranman]

Hi,
Try this:

UPDATE TABLE2 INNER JOIN TABLE1 ON (TABLE1.A = TABLE2.A) AND (TABLE1.B = TABLE2.B) SET TABLE2.C = TABLE1.C;

Tranman

 

[LenRI]

Thanks to both of you, but I have a question stemming from this. What would be the query that would take care of cases in which either field A or B (not both) is NULL? With the queries you gave Access only updates the records in which both A and B are populated.

 

[Tranman]

Well Len,
Joining fields that are null can be a kind of dangerous proposition. Ideally, Null, which is nothing, means nothing (in other words, you don't ascribe characteristics to a field that contains Null, and you don't try to defeat the purpose of Null by making it artificially equal to another Null field).

That being said, you **can** join two null fields (it's not an ideal world), by joining on the NZ of the field, like so:

UPDATE TABLE2 INNER JOIN TABLE1 ON (NZ(TABLE1.A,<SOME IMPOSSIBLE NUMBER>) = NZ(TABLE2.A,<SOME IMPOSSIBLE NUMBER>)) AND (NZ(TABLE1.B,<SOME IMPOSSIBLE NUMBER>) = NZ(TABLE2.B,<SOME IMPOSSIBLE NUMBER>)) SET TABLE2.C = TABLE1.C;

The reason I said <some impossible number>, is that if you just use the NZ function, then Null fields will be joined with fields that contain zero (something I assume you would not want to happen). So, the value of <some impossible number> is any value that will not occur naturally in your data. For instance if TABLE1.A is a day of the week field, make the NZ return an impossible number like 88; etc.

As to not doing the join where both A and B are null, you would just have to add a &quot;Where TABLE1.A is not null or TABLE1.B is not null&quot;.

Good Luck,
Tranman

 

[LenRI]

So are you suggesting that I replace all of the Null entries with <SOME IMPOSSIBLE NUMBER>?

 

[Tranman]

No, the NZ function will do that within the query. Just plug a number that will never happen into each place where the query says <some impossible number>, and make sure they are in pairs (NZ(TABLE1.A,22222) = NZ(TABLE2.A,22222)) AND (NZ(TABLE1.B,99) = NZ(TABLE2.B,99))

This way, each time the query finds a Null in either field A, it treats it like it was a value 22222, and each time there is a Null in either field B, it treats it as if it contained the value 99. So, if both field A's are null, it treats them as if they were both equal to 22222, and you have a valid coequal join.

The 22222 and 99 are just for illustration. It may be that all of the numbers in your A fields are always positive, in which case, any old negative number would serve your purposes. I was just trying to make sure that you didn't use the plain vanilla NZ, which treats every null it finds like a zero, and can cause no end of problems if you have valid fields that actually contain 0's.

Tranman

 

[LenRI]

Thank you very much for your help. Your solution worked like a charm and is really appreciated.