Inserting multiple rows from one table into a single field of another

When I run this:

UPDATE dbo.meta_data
      SET meta_description_se = meta_description_se +
                  (SELECT ean FROM skus WHERE sku_product = 55902
                        AND ean IS NOT NULL      
                  GROUP BY ean )
WHERE product_id = 55902

I get this:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

There are 3 rows returned in the sub query and I am trying to just hold them in a bigger field in another table for reference.  

Is there as way to convert the output of the sub query so it will be allowed to insert/update.  Or do I need to flip this around and iterate through the sub query and write to meta data table on item at a time.
ccleebeltPresidentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Jim HornMicrosoft SQL Server Data DudeCommented:
>There are 3 rows returned in the sub query
Correct, hence the error message as we're assining what T-SQL expects to be a single value into a column.value

>and I am trying to just hold them in a bigger field in another table for reference.  
>Is there as way to convert the output of the sub query so it will be allowed to insert/update.  
Explain for us in great detail what this means, preferably with a data mockup.
ccleebeltPresidentAuthor Commented:
If I run the sub query, the results I get are:

25226000
25222000
25221000

This is the result of the group by which takes 5000 rows from the skus and just gives me unique eans (I don't want to return 5000, the 3 is all I need).

The idea is to save just the 3 records in a single column in the meta_data table so searching for this data can be performed on one column in a table with 500 rows, rather than a different table table with 1000000 rows.   The idea is to roll up the unique values into a varchar(max) so it's all in place and de-duped.
Jim HornMicrosoft SQL Server Data DudeCommented:
>The idea is to save just the 3 records in a single column in the meta_data table
You can't store multiple values in a single column, unless they are stored as a character (i.e. not numeric) comma-delineated such as '25226000, 25222000, 25221000'.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ccleebeltPresidentAuthor Commented:
Yes, understood.  Was wondering if there was some way to take the multiple values and convert them first to a single string within the update using the sub select.

The alternative is to have multiple updates to the meta_data and just concatenate all entries in 3 updates.
Jim HornMicrosoft SQL Server Data DudeCommented:
>and convert them first to a single string within the update
You really need to explain at a Barney-level what you are trying to accomplish here.

To convert multiple values into a comma-delineated string you can use FORM XML Path and STUFF to pull that off, as displayed in this article.   First confirm that this is what you want, then we'll whip up some T-SQL for you.
ccleebeltPresidentAuthor Commented:
That sound right.

In essence I want to turn the sub query:

(SELECT ean FROM skus WHERE sku_product = 55902
                        AND ean IS NOT NULL      
                  GROUP BY ean )

Into just: "25226000, 25222000, 25221000"

In a single operation to avoid having to make three update calls to get the data in.
Jim HornMicrosoft SQL Server Data DudeCommented:
Let me know if this produces the comma-separated strings you are after

SELECT ',' + convert(varchar(50), ean) 
FROM skus 
WHERE sku_product = 55902 AND ean IS NOT NULL 

Open in new window

ccleebeltPresidentAuthor Commented:
That does create three rows, idea is to consolidate into a single row that can be inserted.
Scott PletcherSenior DBACommented:
It's still a very bad idea to concatenate them if you intend to search them.

Just create a separate row for each distinct sku and ean combination.  Something like this:

INSERT INTO dbo.meta_data_eans ( sku_product, ean )
SELECT DISTINCT sku_product, ean
FROM skus
WHERE ean IS NOT NULL

If you need a column in addition to sku to link back to the main metadata table, that could be added to the table as well of course.

Btw, this table should be clustered on ( ean, sku_product ) not by an identity column!
ccleebeltPresidentAuthor Commented:
Ended up just using a cursor to get the data, concatenate it, and insert it into the destination.   Works nicely actually.
Anthony PerkinsCommented:
Ended up just using a cursor to get the data, concatenate it, and insert it into the destination.
When you discover that performance matters consider using a CLR function such as GROUP_CONCAT string aggregate for SQL Server  Yor query would be as simple as:
UPDATE  md
SET     meta_description_se = meta_description_se + eans
FROM    dbo.meta_data md
	INNER JOIN (
		SELECT	sku_product, dbo.GROUP_CONCAT(ean) eans
		FROM	skus
		WHERE	ean IS NOT null
		GROUP BY
			sku_product) s ON md.product_id = s.sku_product

Open in new window


Of course that updates all of them, if you just want to update one as you indicate in your query,  then it just becomes a trivial example.

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
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.