SQL Server: defining a variable

Question

I have a table:

ID Code1 Code2 Result
 1  2201  123
 2  1474  173
 3  5478  103
 4  4147  748
 5  4147  123

I want to populate result such that it should first generate <cat> in ID = 3 & 4 then in next update it should generate <dog> in ID = 4 & 5, now since ID 4 has already <cat>, it must keep this message and add new <dog>. NOTE: i must do that I two(2) update statements. Expected result:

ID Code1 Code2 Result
 1  2201  123  NULL
 2  1474  173  NULL
 3  5478  103  <cat>
 4  4147  748  <cat> <Dog>
 5  4147  123  <dog>

Here is what I try which is obviously wrong, please help

declare @error1 varchar(100)
set @error1 = '<Cat>'
update #tblA
set error = @error1
from #tblA
where ID in (3,4)

declare @error2 varchar(100)
set @error2 = '<Dog>'
update #tblA
set error = @error1 +'+'+@error2 
from #tblA
where id in (4,5) 

Thanks!

Answer 1

The second update doesn't need to know anything special about what the first actually did, just to preserve the column's value (it could be cleaned up to not do the whole '+' thing). In fact, the first could be written the same way.

declare @error1 varchar(100)
set @error1 = '<Cat>'
update #tblA
set error = @error1
from #tblA
where ID in (3,4)

declare @error2 varchar(100)
set @error2 = '<Dog>'
update #tblA
set error = COALESCE(error, '') +'+'+@error2 
from #tblA
where id in (4,5) 

Answer 2

Why does it have to be in two update statements?

UPDATE dbo.MyTable
  SET [Result] = COALESCE([Result], '')
    + CASE WHEN ID IN (3,4)
    AND [Result] NOT LIKE '%<Cat>%'
    THEN '<Cat>' ELSE '' END
    + CASE WHEN ID IN (4,5)
    AND [Result] NOT LIKE '%<Dog>%'
    THEN '<Dog>' ELSE '' END
WHERE ID IN (3,4,5);

Answer 3

Try this:

UPDATE MyTable
SET [Result]= ISNULL([Result],'') + '<Cat>'
WHERE ID IN (3,4);


UPDATE MyTable
SET [Result]= ISNULL([Result],'') + '<Dog>'
WHERE ID IN (4,5);