Eddie Shipman
asked on
Update query help
OK, got these tables in my e-commerce system.
I need to update the ps_product.reference value with the ps_feature_value_lang.valu e where the ps_feature_value_lang.id_l ang = 1 and ps_feature_product.id_prod uct = ps_product.id_product
This query isn't doing what I expected, it replaced them with one single value.
CREATE TABLE `ps_feature_value_lang` (
`id_feature_value` INT(10) UNSIGNED NOT NULL,
`id_lang` INT(10) UNSIGNED NOT NULL,
`value` VARCHAR(255) NULL DEFAULT NULL,
<SNIP INDEXES>
)
CREATE TABLE `ps_feature_product` (
`id_feature` INT(10) UNSIGNED NOT NULL,
`id_product` INT(10) UNSIGNED NOT NULL,
`id_feature_value` INT(10) UNSIGNED NOT NULL,
<SNIP INDEXES>
)
CREATE TABLE `ps_product` (
`id_product` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`id_supplier` INT(10) UNSIGNED NULL DEFAULT NULL,
`id_manufacturer` INT(10) UNSIGNED NULL DEFAULT NULL,
<SNIP columns>
`reference` VARCHAR(32) NULL DEFAULT NULL,
<SNIP more columns>
<SNIP INDEXES>
)
I need to update the ps_product.reference value with the ps_feature_value_lang.valu
This query isn't doing what I expected, it replaced them with one single value.
UPDATE ps_product p
SET p.reference =
(SELECT fvl.value FROM ps_feature_value_lang fvl
INNER JOIN ps_feature_product fp ON fp.id_feature_value = fvl.id_feature_value
WHERE fp.id_feature = 1
AND fp.id_product = p.id_product
AND fvl.id_lang = 1
AND TRIM(p.reference)='')
ASKER
I'm not sure what group_concat does for me, can you explain? SHouldn't I also be using a DISTINCT in the select, though?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
[quote]group_concat will build up a list of all the found values (comma-separated)[/quote]
Who said I wanted a comma sepearated list of values?
Who said I wanted a comma sepearated list of values?
well, please show with data samples what you need.
ASKER
I no longer need this as I am reloading my tables from scratch.
Thanks...
Thanks...
Open in new window