Solved

Update query help

Posted on 2010-08-16
6
304 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 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to update the value of duplicated records (except latest one) 2 34
remote mysql 8 49
MySQL Finding Duplicates in a Normalized Database 6 39
MySQL 6 43
Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

742 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