Update Query Failure

[dyrueta]

Hi All --

Is there any way to prevent an Update Query from completely failing if one or some of the fields in the source table are null?

Example: Here's the SQL statement for the update Query:
UPDATE tblRESULT AS RS INNER JOIN dbo_PRM_MASTER__EMPLOYEE AS EMP ON RS.EMPLOYEE = EMP.EMPLOYEE SET RS.EMPLOYEE_NAME = EMP.EMPLOYEE_NAME, RS.ADDRESS_1 = EMP.ADDRESS_1, RS.ADDRESS_2 = EMP.ADDRESS_2, RS.CITY = EMP.CITY, RS.ZIP_CODE = EMP.ZIP_CODE, RS.STATE = EMP.STATE, RS.PHONE_NUMBER = EMP.PHONE_1, RS.SOCIAL_SECURITY_NUMBER = EMP.SOCIAL_SECURITY_NUMBER

In some cases, there's an null value or empty string (I'm not sure which) in the EMP.ADDRESS_2 field. As a result, when the query executes, none of the other fields are updated -- the update fails. That seems unfair

Other than breaking this single query into separate update queries for each destination field which I wish to update, is there any way to push the update through?

Many thanks!

 

[jdraw]

You could use an iif,

iif (isNull(EMP.ADDRESS_2)," ",EMP.ADDRESS_2 )

which will give you a blank (space) if EMP.ADDRESS_2 is NULL

 

[dyrueta]

Like this ---?

UPDATE tblRESULT AS RS INNER JOIN dbo_PRM_MASTER__EMPLOYEE AS EMP ON RS.EMPLOYEE = EMP.EMPLOYEE SET...
iif(isNull(EMP.ADDRESS_2), RS.ADDRESS_2 = " ", RS.ADDRESS_2 = EMP.ADDRESS_2)...

 

[jdraw]

UPDATE tblRESULT AS RS INNER JOIN dbo_PRM_MASTER__EMPLOYEE AS EMP ON RS.EMPLOYEE = EMP.EMPLOYEE
SET
RS.EMPLOYEE_NAME = EMP.EMPLOYEE_NAME
, RS.ADDRESS_1 = EMP.ADDRESS_1
, RS.ADDRESS_2 = iif(IsNull(EMP.ADDRESS_2),"",EMP.ADDRESS_2)
, RS.CITY = EMP.CITY
, RS.ZIP_CODE = EMP.ZIP_CODE
, RS.STATE = EMP.STATE
, RS.PHONE_NUMBER = EMP.PHONE_1
, RS.SOCIAL_SECURITY_NUMBER = EMP.SOCIAL_SECURITY_NUMBER