I have a table dbo.mdx_physdirectory with 6 fields:
When I look at the table in SQL, some of the fields say NULL and some are just blank.
I ran the following update query to combine specialty fields 1-5 (separated by commas) into a single field (specialty0)
SET specialty0 = specialty1 + ', ' + specialty2 + ', ' + specialty3 + ', ' + specialty4 + ', ' + specialty5
WHERE (specialty1 IS NULL) OR
(specialty1 = '') OR
(specialty2 IS NULL) OR
(specialty2 = '') OR
(specialty3 IS NULL) OR
(specialty3 = '') OR
(specialty4 IS NULL) OR
(specialty4 = '') OR
(specialty5 IS NULL) OR
(specialty5 = '')
When I run the query, though, only the rows where one or more of the specialty fields (1-5) =blank update and those where one or more of the specialty fields (1-5) = Null do not update. Also, there are commas with blank spaces wherever any of the fields were blank.
Can someone please tell me how to get all the fields to update (even if some of the fields =blank or =Null) and how to not include commas when the field is blank or null?