mysql GROUP_CONCAT update select help

[slasher]

Hi

anyone know why this doesn't work.... it doesn't update any rows even though narrowby table has all of the nby_pid values

update narrowby set type_155=1 where nby_pid=(Select GROUP_CONCAT(product_id SEPARATOR ' or nby_pid=') AS 'nby_pid' From product_description Where name Like 'VTR%')

the above doesn't work then if I run this update with the sub query included it works (like below)

same query but broken down without the sub-select ....which does update fine

update narrowby set type_155=1 where nby_pid=12501 or nby_pid=12536 or nby_pid=12504 or nby_pid=12471 or nby_pid=12513 or nby_pid=12607 or nby_pid=12605 or nby_pid=12606 or nby_pid=12493 or nby_pid=12604 or nby_pid=12574 or nby_pid=12480 or nby_pid=12596 or nby_pid=12608 or nby_pid=12595 or nby_pid=12503 or nby_pid=12481 or nby_pid=12535 or nby_pid=12482 or nby_pid=12476 or nby_pid=12472 or nby_pid=12613 or nby_pid=12609 or nby_pid=12538 or nby_pid=12496 or nby_pid=12537 or nby_pid=12577

nby_id is primary & index

tried both 5.0.77 & 5.1.33 mysql versions

Thanks in advance

Slasher

 

[topdog@OC]

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

"This function returns a string result ..."

So your query is turned into this resolved form:
update narrowby set type_155=1 where nby_pid="some string value"

And it can find no matches for that case.

You want an IN (...) or EXISTS clause instead.

select a from b where c in (select d from e)

In this case, all values returning in subquery column d are matched against c. You should only return one column in an IN() subquery.

Alternatively:

select a from b where exists (select null from e where d = c)

These are overly simplified examples, I'd recommend using aliases on tables and columns to avoid any possible ambiguities (same column appearing in multiple tables), like "select a from sometable b where exists (select null from othertable e where e.d = b.c)" would be better.

Depending on your purposes and DBMS, the EXISTS clause MAY perform better than an IN() clause and is preferred as a rule of thumb (that doesn't make it always superior or best though, just saying).

 

[slasher]

Thanks... I do appreciate your help

Sorry I'm still learning .... is this what you mean't?

doesn't work

update narrowby set type_155=1 where nby_pid in ((Select GROUP_CONCAT(product_id) From product_description Where name Like 'VTR%'))

works perfectly but need the sub select to work

update narrowby set type_155=1 where nby_pid in (12501,12536,12504,12471,12513,12607,12605,12606,12493,12604,12574,12480,12596,12608,12595,12503,12481,12535,12482,12476,12472,12613,12609,12538,12496,12537,12577)

Sorry for sounding
I can't get my head around why if I break the above query down it works fine & the select works fine but joining the 2 together is a different matter

Can you give me an example with the query above how it should be written?

Thanks again

 

[slasher]

sorted it.....concat not group_concat

update narrowby set type_155=1 where nby_pid in ((Select CONCAT(product_id) as 'id' From product_description Where name Like 'VTR%'))

Many Thanks again

 

[topdog@OC]

Given the original of:
update narrowby set type_155=1 where nby_pid=(Select GROUP_CONCAT(product_id SEPARATOR ' or nby_pid=') AS 'nby_pid' From product_description Where name Like 'VTR%')

I'd have done:

update narrowby set type_155=1 where exists (select null from product_description where name like 'VTR%' and product_id = nby_pid)

-or-

update narrowby set type_155=1 where nby_pid in (select product_id from product_description where name like 'VTR%')

The latter being pretty much the same as you've come to, but there doesn't seem to be a need for any functions like CONCAT().

 

[slasher]

Thanks for all of the repies

Trying to format in this way (see below)

This query works but would like to be able to put some li span tags like below I know concat_ws allows formatting to this extent but group_concat doesn't

select atributes.Code, group_concat( atributes.name, atributes.value separator ' ' ) as `id` from atributes group by atributes.Code;

Code   | name               | value 
-----------------------------------------
100246 | Item model number  | runner 9001
100246 | Weight             | 982g
100246 | Batteries          | 2 included
100557 | Item model number  | runner 9007
100557 | Weight             | 112g
100557 | Batteries          | 3 included

Code   | id             
-----------------------------------------
100246 |<li id='name'><span class='spec'>Item model number</span>runner 9001</li><li id='name'><span class='spec'>Weight</span>982g</li><li id='name'><span class='spec'>Batteries</span>2 included</li>
100557 |<li id='name'><span class='spec'>Item model number</span>runner 9007</li><li id='name'><span class='spec'>Weight</span>112g</li><li id='name'><span class='spec'>Batteries</span>3 included</li>

any ideas?

Thanks in advance

 

[topdog@OC]

You'd have to use the old fashioned pivot/crosstab method I think.

It goes something like this:

select
  code,
  max(case when name = 'Item model number' then value else null) as item_model_number, 
  max(case when name = 'Weight' then value else null) as weight, 
  max(case when name = 'Batteries' then value else null) as batteries
from
  atributes
group by code

(I don't have mysql handy at the moment so I'm going by memory, but this is roughly equivalent to Oracle's decode() / group by method for the same result).

That would then get you 1 row per code with 3 other columns to represent each of the other items. In your PHP you'd then iterate over this result set and output your formatted HTML inserting the field values you retrieve from this query at the appropriate places.

You could try to construct all the HTML formatting in the query itself, but that's not a good design model. Should try to keep the view / presentation code separate from the model / business logic code for ease of maintenance, otherwise it's too easy to need to tweak one and break and/or require massive reworking of the other unnecessarily.

The 'disadvantage' with this approach, is that you need to generate the SQL in a fairly rigid style compared to the apparent flexibility of the original data (to be able to expand to more than 3 specific and ordered entries per code to more). To overcome that you'd have to write code that inspected the scope of 'name' column entries possible in the table, then dynamically constructs the SQL for the case when / group by to be able to deal with a flexible range of possible column outputs.

I don't think, however, you'll get what you want with manipulation of the group_concat() function (or related ones) for this. At least in part due to the fact that your 'separators' between items consist of a prefix, 'tween, and suffix parts, and I believe the various concat functions only allow for a single separator string between every column value it joins together.

 

[topdog@OC]

Ok, mysql to hand today.

Test data setup:

create table atributes (code bigint, name varchar(32), value varchar(32))
/

insert into atributes (code, name, value) values (
100246, 'Item model number', 'runner 9001' 
)
/
insert into atributes (code, name, value) values (
100246, 'Weight', '982g' 
)
/
insert into atributes (code, name, value) values (
100246, 'Batteries', '2 included' 
)
/

insert into atributes (code, name, value) values (
100557, 'Item model number', 'runner 9007' 
)
/
insert into atributes (code, name, value) values (
100557, 'Weight', '112g' 
)
/
insert into atributes (code, name, value) values (
100557, 'Batteries', '3 included' 
)
/

commit
/

Query (had to make a couple of syntax changes to this as my memory was a little off - basically had a typo missing off the 'end' keywords):

select
  code,
  max(case when name = 'Item model number' then value else null end) as item_model_number, 
  max(case when name = 'Weight' then value else null end) as weight, 
  max(case when name = 'Batteries' then value else null end) as batteries
from
  atributes
group by code

Results:

 code     item_model_number     weight     batteries    
 -------  --------------------  ---------  ------------ 
 100246   runner 9001           982g       2 included   
 100557   runner 9007           112g       3 included   

 2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 10/ms] 

 [Executed: 20/12/11 14:00:24 GMT ] [Execution: 40/ms]

You could as I said further wrap this with a "select code, concat(..., ..., ..., ..., ..., etc.) from [subquery]" in order to embed the html formatting into the query results, but I would advise against it and instead just return the columns as in the query above, and do that formatting in PHP as you iterate over the rows returned.

 

[slasher]

Sorry had to go away family issues

Thanks for all of the replies

Trying to update from 1 table to another with a sub select query

update test, atributes
set
test.spec=(SELECT GROUP_CONCAT(CONCAT_WS('','<li><span class="spec">', atributes.FriendlyName3, '</span>', atributes.AttributeValue,'</li>') SEPARATOR '') as spec FROM atributes group by Code)
where 
test.Code=atributes.Code

get error 1242 subquery returns more than 1 row

How do I get this to work, is it possible?

Thanks in advance

 

[RobH]

You also need to restrict the subquery as well as the main query using a WHERE clause. The best way to figure out what the subquery is returning is to just run it on it's own, you'll see straight away that it has no restrictions and returns everything in the atributes table.

UPDATE
  test,
  atributes

SET
  test.spec = (
    SELECT
      GROUP_CONCAT(
        CONCAT_WS('' , '<li><span class="spec">', atributes.FriendlyName3, '</span>', atributes.AttributeValue, '</li>')
        SEPARATOR ''
      ) AS spec
     
    FROM
      atributes

    -- Restrict the subquery based on the current row in the main query
    WHERE atributes.Code = test.Code

    GROUP BY atributes.Code
)

WHERE
  test.Code = atributes.code

 

[slasher]

Perfect.....many thanks Rob, understand it now