Update column with values from field in the same table if field is not null-CodePudding

Thank you all in advance, I have been trying to use the query in the post below to update null values in the name_field column using the same column only if the values are not null

Update Field based on Same Field not Null

My table has ParcelID_Field, Name_Field and Address_Field sometimes the Namefield is blank. The address and ID fields are always populated. Whenever the Name field Is NULL I would like to use the information that is there to populate the NULL fields

This query almost works, but its says 0 rows affected when ran. The select within the ( ) works perfectly just no values are updated.

update [DMSEngine].[dbo].[IndexForm_ePermitsResults] 
set Name_Field = (select b2.Name_Field
                    from [DMSEngine].[dbo].[IndexForm_ePermitsResults] b2
                    where b2.Name_Field is not null and
                          b2.ParcelID_Field = ParcelID_Field and
                          ParcelID_Field = 12257
                   )
where ParcelID_Field = 12257 and Name_Field is null;

ParcelID_Field	Name_Field	AddressField
111	smith	1 street name
111		1 street name
111		1 street name
111	smith	1 street name
222		3 street name
222	jacobs	3 street name
222		3 street name
222		3 street name

CodePudding user response：

When editing the data in the table, try the Select query alone first, so you can know if it returns the right data.

About this query: Why do you have "ParcelID_Field = 12257"? This "where" condition will give you only a field where the Name_Field is null AND ParcelID_Field is 12257.

CodePudding user response：

I figured it out. The select was off as intimated by the posted who questioned above. So I changed the select statement but even that threw an error. Subquery returned more that 1 value. This is not permitted... So I read up on that error and included MAX in my query and it worked.

The correct answer is

update [DMSEngine].[dbo].[IndexForm_ePermitsResults] 
set Name_Field = (select MAX (b2.Name_Field)
                from [DMSEngine].[dbo].[IndexForm_ePermitsResults] b2
                where b2.Name_Field <>'' and
                      b2.ParcelID_Field = ParcelID_Field and
                      ParcelID_Field = 12257
               )
where ParcelID_Field = 12257 and Name_Field = ' ';