Have you looked at the GROUP_CONCAT() function? (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html)
It would make your update look like: CREATE TABLE tmpList SELECT ts.product_uid as UID, Group_Concat(ev.Text) as newtext FROM X_Search.text_search as ts INNER JOIN especee as es ON ts.product_uid = es.ProdID INNER JOIN evocee as ev ON es.BodyID = ev.ID GROUP BY ts.product_uid UPDATE X_Search.text_search as ts INNER JOIN tmpList tl on tl.uid = ts.product_uid SET ts.txt = tl.newtext However, you **MUST** ensure that X_Search.text_search has a large enough "txt" column to take all of the values. If this is not possible then you only want to insert/update with your maximum # of characters to prevent field overflow. You probably also need to predeclare the temp table to make sure the text column will be wide enough to take the concatenated results. CREATE TABLE tmpList ( UID bigint, newtext _correctly size this field_ ) INSERT tmpList (UID, newtext) SELECT (....same as above) Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Grimshaw <[EMAIL PROTECTED]> wrote on 07/20/2004 06:35:17 AM: > Further to my Full Text question the other day, I'm trying to add the > parimetric data to the field that gets searched. > > We have a script that runs periodically to update this table. I can do > what I want in that script no problem, but it would be more elegent if > I could acheive the same results with 1 query. > > As an example, lets take a random product and look at the parimetric > data for it. > > SELECT es.ProdID, ev.Text > FROM especee as es > INNER JOIN evocee as ev ON (es.BodyID=ev.ID) > WHERE es.ProdID = 45607; > > +--------+---------------------------------------------------+ > | ProdID | Text | > +--------+---------------------------------------------------+ > | 45607 | Limited warranty - 1 year | > | 45607 | 1 year warranty | > .... > | 45607 | External | > | 45607 | POTS filter | > +--------+---------------------------------------------------+ > > > I want to concat all the values of "Text" into 1 row in another table. > I have this so far: > > UPDATE X_Search.text_search as ts > INNER JOIN especee as es ON (ts.product_uid = es.ProdID) > INNER JOIN evocee as ev ON (es.BodyID=ev.ID) > SET ts.txt = CONCAT(ts.txt, ev.Text) > WHERE ts.product_uid = 45607; > > but all that gets appended is the 1st row, ie "Limited warranty - 1 year" > > Is what I'm trying to to possible, and if it is, am I going down the > right road? > > -- > -S > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >