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.

possible duplicate of [How to make a query with group\_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — A ツ, Jun 17 '15 at 15:51
No, you cannot use a variable to perform a multi-row update like this. Look into doing your concatenation with "FOR XML PATH" — Tab Alleman, Jun 17 '15 at 15:52
@Aツ that statement isn't an update, I need my statement in an update - rather then simple select which that question has. — YelizavetaYR, Jun 17 '15 at 16:03

score 0 · Accepted Answer · answered Jun 17 '15 at 15:54

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

Nested SQL with Coalesced data part of an update statement

1 Answers1