Nested SQL with Coalesced data part of an update statement

Question

I have the following set up

create table #work
(
customer_no int,
attended_conf varchar(100) -- ADDED LR 6/16/15
)

The #work table is filled - customer_no is filled, attend_conf is null until a later update statement.

What I would like to do is update the attended_conf field with the concatenated strings. (The code should take all the key_values for a given keyword_no and concatenate them. This code works:

declare @const_str varchar(255)
select @const_str = ''


SELECT  @const_str = @const_str + COALESCE(e.key_value + '; ', '') 
from    TX_CUST_KEYWORD e 
join    T_CUSTOMER s on e.customer_no=s.customer_no
where   e.customer_no = 86038213 
and     keyword_no = 704

select @const_str

output for the one line looks something like this:

(No column name)
2003-2004; 2007-2008; 2014-2015; 2017-2018; 

However, when i try to consolidate with the update statement. My initial update statement (that needs changing).

update  x       
set     attended_conference = @const_str 
from    #work x
join    TX_CUST_KEYWORD p on x.customer_no = p.customer_no
where   keyword_no = 704

to do something like this ...

update  x       
set     attended_conference = @const_str 
from    #work x
join    (select @const_str = @const_str + COALESCE(e.key_value + '; ', ''), 
                    s.customer_no
            from    TX_CUST_KEYWORD e 
                    join    T_CUSTOMER s on e.customer_no=s.customer_no
                    where       keyword_no = 704) as a
where   x.customer_no = a.customer_no  

doesn't work.

My question is different from the other question is because i need to do the COALESCE/concatenate in an update statement not a simple select. I can't just do a select. The other tickets seem to show a select statement, I can get the select OK - but my issue is with updating.

Answer 1

You can use FOR XML to concatenate

update  x       
set     attended_conf = (select e.key_value + '; ' as "text()"
                         from TX_CUST_KEYWORD e
                         where e.customer_no = x.customer_no
                         and   e.keyword_no = 704
                         order by e.key_value
                         for xml path(''))
from    #work x