CODEWITHSUNDEEP
sqlsql-server
Biswa
Biswa

Reputation: 331

I want to update table but before update it should check null value

Hi i want to update a table column by concatenating 3 columns value but the problem is how can i stop the update if any of the 3 columns has null value.

my query is given below where i want to update record of ind_ref 123 but if any of the value of the 3 column which i concatenating is null then it shouldn't update the display_name column. How can i achieve it? 

update individual_loc
Set individual_loc.display_name=individual_loc.address1+','+INDIVIDUAL_LOCATION.TOWN+','+INDIVIDUAL_LOCATION.COUNTY
where individual_loc.ind_ref=123

Upvotes: 0

Views: 84

Answers (6)

Balaji
Balaji

Reputation: 172

If you are trying to avoid null issues just check for isnull(individual_loc.address1,'')

If its something else try : IIF((isnull(address1,'')='' OR isnull(TOWN,'')='' OR isnull(COUNTY,'')=''), UPDATE STATEMENT, PRINT 'False')

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You could also check null values by using coalesce() function 

update individual_loc
Set individual_loc.display_name=individual_loc.address1+','+INDIVIDUAL_LOCATION.TOWN+','+INDIVIDUAL_LOCATION.COUNTY
where individual_loc.ind_ref=123 
and coalesce(individual_loc.address1+','+INDIVIDUAL_LOCATION.TOWN+','+INDIVIDUAL_LOCATION.COUNTY, 0) <> 0

Upvotes: 0

Sparky
Sparky

Reputation: 15105

try this

update individual_loc
Set individual_loc.display_name=individual_loc.address1+','+
    individual_loc.TOWN+','+individual_loc.COUNTY
where individual_loc.ind_ref=123 
AND address1 is not null
AND town is not null
AND county is not null

Basically, only perform the update if the fields aren't null

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46233

One method is with COALESCE:

UPDATE individual_loc
SET individual_loc.display_name=COALESCE(individual_loc.address1+','+INDIVIDUAL_LOCATION.TOWN+','+INDIVIDUAL_LOCATION.COUNTY, individual_loc.display_name)
where individual_loc.ind_ref=123;

Upvotes: 0

Derrick Moeller
Derrick Moeller

Reputation: 4960

Simply add a check for NULL in your WHERE clause.

UPDATE individual_loc
SET display_name = address1 + ',' + TOWN + ',' + COUNTY
WHERE ind_ref = 123 AND address1 IS NOT NULL AND TOWN IS NOT NULL AND COUNTY IS NOT NULL

Upvotes: 3

Hasmukh Viradiya
Hasmukh Viradiya

Reputation: 174

UPDATE  individual_loc
SET     individual_loc.display_name = CASE WHEN ISNULL(individual_loc.address1,
                                                   '') != ''
                                            AND ISNULL(INDIVIDUAL_LOCATION.TOWN,
                                                       '') != ''
                                            AND ISNULL(INDIVIDUAL_LOCATION.COUNTY,
                                                       '') != ''
                                       THEN individual_loc.address1 + ','
                                            + INDIVIDUAL_LOCATION.TOWN
                                            + ','
                                            + INDIVIDUAL_LOCATION.COUNTY
                                       ELSE individual_loc.display_name
                                  END
WHERE   individual_loc.ind_ref = 123

Upvotes: 2

Related Questions