Updating a column based on multiple columns from another table

[Dabase]

Hi,

I am trying to update a column in a table based multiple columns of another table.

Below is the script that I am using:

UPDATE tblNews
SET N_PS_IDs =
(case isnull(InfraNewsMigration.dbo.thedatafile.Col027, '0')
when '1' then '11'
end) + ', ' +
(case isnull(InfraNewsMigration.dbo.thedatafile.Col028, '0')
when '1' then '12'
end) + ', ' +
(case isnull(InfraNewsMigration.dbo.thedatafile.Col029, '0')
when '1' then '9'
end) + ', ' +
(case isnull(InfraNewsMigration.dbo.thedatafile.Col036, '0')
when '1' then '14'
end)
FROM tblNews, InfraNewsMigration.dbo.thedatafile
WHERE tblNews.N_ID = InfraNewsMigration.dbo.thedatafile.[Col001]

This is not updating my column, any ideas where I am going wrong?


Thanks
Dabase

 

[hneal98]

You could use something like this:

UPDATE tblNews
SET N_PS_IDs = 
	case when isnull(InfraNewsMigration.dbo.thedatafile.Col027, '0') = '1' then '11'
		 when isnull(InfraNewsMigration.dbo.thedatafile.Col028, '0') = '1' then '12'
		 when isnull(InfraNewsMigration.dbo.thedatafile.Col029, '0') = '1' then '9'
		 when isnull(InfraNewsMigration.dbo.thedatafile.Col036, '0') = '1' then '14'
	end
FROM tblNews, InfraNewsMigration.dbo.thedatafile
WHERE tblNews.N_ID = InfraNewsMigration.dbo.thedatafile.[Col001]

However, you don't indicate what is to happen if the fields do not meet those values. What if they are null?

 

[Dabase]

Hi hneal98,

Thanks for the reply.

What I am trying to do is concatenating the results into N_PS_IDs separated by ', '. If the field is null then '0' should be written, so that the end result on N_PS_IDs would something like:

11, 12, 0, 14



Thanks
Dabase