?
Solved

Update query help

Posted on 2010-08-16
6
Medium Priority
?
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 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