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