How can I update a field not contained in query

I have a MS SQL query like this (please apologize for german field names):

SELECT dw1.belegnummer,
  SUBSTRING((SELECT ',' + dw2.barcode2 FROM [dbo].[dtable] dw2 
    WHERE dw2.belegnummer = dw1.belegnummer AND barcode2 LIKE 'A%' FOR XML PATH('')),2,8000))
  FROM (SELECT DISTINCT belegnummer FROM [dbo].[dtable] WHERE barcode2 LIKE 'A%' AND belegnummer IS NOT NULL) dw1

Open in new window


This is used to concatenate field values of barcode2 field in the case that more than one unique belegnummer record exists (in fact it does it also if only one exists, but that's no problem). The query works fine, but I need to write the result (the second 'field' from the above SELECT) into a field (additionalBC) of that record.

I can't get it working. Every time I try to use UPDATE, MS SQL tells me that the update field additionalBC is unknown. As I didn't do much UPDATE stuff in the past, I think it's just a little thing missing, but I'm not an expert...

TIA,
Michael
mkmkoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try using a CTE:
;WITH CTE_additionalBC (belegnummer, additionalBC)
AS 
(
SELECT dw1.belegnummer,
	  SUBSTRING((SELECT ',' + dw2.barcode2 
		FROM [dbo].[dtable] dw2 
		WHERE dw2.belegnummer = dw1.belegnummer AND barcode2 LIKE 'A%' FOR XML PATH('')),2,8000)
FROM 
	(SELECT DISTINCT belegnummer 
	FROM [dbo].[dtable] 
	WHERE barcode2 LIKE 'A%' AND belegnummer IS NOT NULL) dw1
)
UPDATE t
SET t.additionalBC=c.additionalBC
FROM dtable t
	INNER JOIN CTE_additionalBC c ON t.belegnummer = c.belegnummer

Open in new window

0
Scott PletcherSenior DBACommented:
You can use OUTER APPLY.  SQL should cache the results -- via a "lazy spool" -- so that subsequent calls with the same belegnummer do not result in extra processing.

UPDATE dw1
SET additionalBC = oa1.additionalBC
FROM [dbo].[dtable] dw1
OUTER APPLY (
    SELECT SUBSTRING((SELECT ',' + dw2.barcode2
    FROM [dbo].[dtable] dw2
    WHERE dw2.belegnummer = dw1.belegnummer AND
        dw2.barcode2 LIKE 'A%'
    FOR XML PATH('')),2,8000) AS additionalBC
) AS oa1
WHERE dw1.barcode2 LIKE 'A%' AND --??verify this is needed/wanted here
    dw1.belegnummer IS NOT NULL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mkmkoAuthor Commented:
Scott's solution is easier to use in our case, Vitor's may work too. In this case I will split my points and say 'thank you very much' to both of you.

Michael
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.