[RESOLVED] Update query help, please

[mrbaseball34]

OK, got these tables in my e-commerce system.

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,
	`reference` VARCHAR(32) NULL DEFAULT NULL,
        <SNIP other columns>
        <SNIP INDEXES>
)

I need to update the ps_product.reference value with the
ps_feature_value_lang.value where the ps_feature_value_lang.id_lang = 1
ps_feature_product.id_feature = 1 and ps_feature_product.id_product = ps_product.id_product

This query isn't doing what I expect, 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)='')

[sneakyimp]

This looks tough.

Maybe you could try this query?

SELECT 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)='') AS new_value
FROM ps_product p 

[mrbaseball34]

Thanks, sneaky, however, I decided to just clear out the DB and start over.

[sneakyimp]

Good luck.

Boy that Tom guy is a real tool, isn't he?

[mrbaseball34]

Just an old fart, stuck in his ways. I'm an old fart, too, but at least I don't piss on anyone trying to help me.