Update Query the correct way ?

[SteveE]

I've search about in various forums but I haven’t found any answers to this specific question.

I have 2 tables Joined by an id field. Table 1 has 4 fields that MAY require updating.

Each field in table 1 is updated from table2 but only if the equivalent field is Null in table 1.
I can update all 4 fields from table 2 at once but I only want to update those that are missing data (i.e. null) if I set each field criteria as Is Null then the record will only update if all 4 are null.
I have this running without issue by having 4 separate update queries called 1 after the other. BUT I am wondering if there is a better way of doing this?

any advice welcomed
Steve

 

[SOS]

Four update queries it is.

 

[pbaldy]

This is utterly untested, but I wonder if one update query with something like this would work:

SET FieldName = Nz(Table1.FieldName, Table2.FieldName)

It would needlessly update fields to themselves, but might be better than 4 updates.

 

[SOS]

Interesting concept - so outrageous it just might work

 

[SteveE]

Paul/SOS thanks for the replies, but to be honest I dont understand where the SET FieldName = Nz(Table1.FieldName, Table2.FieldName) is used (appoligies for being thick)

 

[pbaldy]

Get one of your update queries into design view, then switch to SQL view. You will see the basic structure of:

UPDATE TableName
SET FieldName = Whatever
WHERE FieldName Is Null

so the SET statement that I suggested would replace the original, and you would drop the WHERE clause (and add the other 3 fields). You could also try the Nz(...) portion in the "Update to" section in design view. So in SQL view:

UPDATE TableName
SET Field1 = Nz(...), Field2 = Nz(...), Field3 = Nz(...), Field4 = Nz(...)

 

[SteveE]

OK thanks a lot I will, have a go when back in work Monday .
have a good weekend
Steve

 

[pbaldy]

Have a good weekend. Post back, as I'd like to know if my shot-in-the-dark actually hit the target.

 

[SOS]

Have a good weekend. Post back, as I'd like to know if my shot-in-the-dark actually hit the target.

and if there was any collateral damage

 

[SteveE]

I have tried the query as below on my test version and it appears to work fine

UPDATE ExportsSheet INNER JOIN Book ON ExportsSheet.[ID] = Book.[ASN] SET ExportsSheet.Vehicle = Nz([Book].[Vehicle]), ExportsSheet.Driver = Nz([Book].[Driver]), ExportsSheet.TrlNo = Nz([Book].[TrlIDout]), ExportsSheet.Carrier = Nz([Book].[Carrier])
WHERE (((ExportsSheet.ReloadDate)>Date()-14));

I will do some more testing before installation, the only problem I have is a warning of cannot update 3 records due to validations issues, which I will take a look at tomorrow. All being well it seems to be a result !!

thanks

Steve