Solved

Update query help

Posted on 2010-08-16
6
294 Views
Last Modified: 2012-05-10
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,
        <SNIP columns>
	`reference` VARCHAR(32) NULL DEFAULT NULL,
        <SNIP more columns>
        <SNIP INDEXES>
)

Open in new window


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 and ps_feature_product.id_product = ps_product.id_product

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)='')

Open in new window

0
Comment
Question by:EddieShipman
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33449320
I presume you want this:
UPDATE ps_product p 
SET p.reference = 
  (SELECT group_concat(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)='')

Open in new window

0
 
LVL 26

Author Comment

by:EddieShipman
ID: 33449604
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?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 33452171
well, you wrote:
>, it replaced them with one single value.

group_concat will build up a list of all the found values (comma-separated)

>SHouldn't I also be using a DISTINCT in the select, though?
SELECT group_concat(distinct fvl.value)

should do that ...
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Author Comment

by:EddieShipman
ID: 33453474
[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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33453507
well, please show with data samples what you need.
0
 
LVL 26

Author Closing Comment

by:EddieShipman
ID: 33454596
I no longer need this as I am reloading my tables from scratch.
Thanks...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question