condition update cells within the same column

Question

I need to update cells within a specific column based upon ids in another column. The column names are Prod_ID, Lang_ID and Descr:

Prod_ID | Lang_ID | Descr
--------+---------+------
A101    | 1       | TextA
A101    | 2       | TextB
A101    | 3       | TextC

For a group of rows with the same Prod_ID, I need to replace all subsequent descriptions (Descr column) with the description of the first row. The row with the correct description has always Lang_ID = 1. Also, the table may not be sorted by Lang_ID.

Example: TextA (Lang_ID = 1) should replace TextB and TextC because the Prod_IDs of the rows match.

Next time please specify all important conditions in your question. It takes a lot of time to gather the required information from your comments and collect them in an edit. — speakr, Jul 11 '12 at 08:41

Chris Trahey · Answer 1 · 2012-07-11T14:56:55.980

2

You mentioned in a comment elsewhere that the "master" lang_id is always 1. That simplifies things greatly, and you can do this with a simple self-join (no subqueries :-)

This query selects all lang_1 rows, then joins them with all non-lang_1 rows of the same prod_id and updates those.

If Lang_ID=1 is always the "first"

UPDATE products 
  LEFT JOIN products as duplicates
         ON products.Prod_ID=duplicates.Prod_ID 
        AND duplicates.Lang_ID != 1
SET duplicates.Descr = products.Descr
WHERE products.Lang_ID = 1

edit: If Lang_ID=1 may not be the "first"

you can join the table to itself via a an intermediate join which finds the lowest Lang_ID for that row. I have called the intermediate-join "lang_finder".

UPDATE products 
  LEFT JOIN (SELECT Prod_ID, MIN(Lang_ID) as Lang_ID FROM products GROUP BY Prod_ID) as lang_finder
         ON products.prod_id=lang_finder.prod_id 
  LEFT JOIN products as cannonical_lang 
         ON products.Prod_ID = cannonical_lang.Prod_ID
        AND lang_finder.Lang_ID = cannonical_lang.Lang_ID
SET products.Descr = cannonical_lang.Descr

Note that while it does use a subquery, it does not nest them. The subquery essentially just adds a column to the products table (virtually) with the value of the lowest Lang_ID, which then allows a self-join to match on that. So if there were a product with Lang_ID 3, 4, & 5, this would set the Descr on all of them to whatever was set for Lang_ID 3.

edited Jul 11 '12 at 14:56

answered Jul 11 '12 at 07:45

Chris Trahey

18,202
1
42
55

-1 : What if I have first row with Lang_id as 2? What if I have another rows with Prod_ID = 'A102' and Lang_ID as 4 & 5? – Fahim Parkar Jul 11 '12 at 08:11
@FahimParkar No need to downvote - ctrahey's query is fully working. The OP said that the row with a correct description has always Lang_ID = 1. The Descr column of other rows with same Prod_ID and Lang_ID != 1 will be set to the correct one. This is what was asked for. I edited the question for clarification. – speakr Jul 11 '12 at 08:32
@FahimParkar I would not downvote answers unless I was certain that they were wrong and wouldn't help in some other case. Downvotes are necessary but are often cast to easily. – speakr Jul 11 '12 at 08:50
I have edited my answer (not my original query) to add the query I was original conceiving for this until I noticed the asker mentioned Lang_ID=1 is always canonical. @FahimParkar this means you can change your vote; if you'd like. – Chris Trahey Jul 11 '12 at 14:58
This appears to do what I was looking for when i attempt here: http://sqlfiddle.com/#!2/9b632/1 However is not working in phpmyadmin – user1515104 Jul 11 '12 at 21:41

score 1 · Answer 2 · answered Jul 11 '12 at 08:09

1

How about this?

UPDATE myTable dt1, myTable dt2 
SET dt1.Descr = dt2.Descr
WHERE dt1.Prod_ID=dt2.Prod_ID;

Demo at sqlfiddle

answered Jul 11 '12 at 08:09

Fahim Parkar

30,974
45
160
276

Your query doesn't work as intended if the table is not sorted by Lang_ID (see [this comment](http://stackoverflow.com/questions/11416780/condition-update-cells-within-the-same-column/11417475#comment15073975_11417475)). – speakr Jul 11 '12 at 08:25
@speakr : if Lang_id is 3 and that is first then OP wants data of 3 to be copied for rest cells.. right?? – Fahim Parkar Jul 11 '12 at 08:31
@speakr : Do you mean, in my case, always Descr should be Text A? – Fahim Parkar Jul 11 '12 at 08:32
Again, please read [this comment](http://stackoverflow.com/questions/11416780/condition-update-cells-within-the-same-column#comment15073975_11417475). The correct description is always in the row where Lang_ID = 1 (i.e. has the smallest value). Your query needs the correct row to be the first one of a group of rows there Prod_ID is the same, but the OP stated that this may not be the case. – speakr Jul 11 '12 at 08:38

score 0 · Accepted Answer · edited May 23 '17 at 12:12

0

Assuming that the correct description is always in the row of a group of rows with the same Prod_ID where Lang_ID has the smallest value, this MySQL query should work:

UPDATE your_table AS t1
    JOIN (
        SELECT Prod_ID, Descr
        FROM (
            SELECT *
            FROM your_table
            ORDER BY Lang_ID
        ) AS t3
        GROUP BY Prod_ID
    ) AS t2
    ON t1.Prod_ID = t2.Prod_ID
SET t1.Descr = t2.Descr;

The above can be used e.g. if Lang_ID is a primary or unique key. It also works if the corresponding Lang_ID has always the same minimum value (e.g. = 1) but in that case much less complex queries like this one are possible.

edited May 23 '17 at 12:12

Community

1
1

answered Jul 10 '12 at 16:11

speakr

4,141
1
22
28

What if it is not always the first row in a group of rows? The Lang_ID that should overwrite the others (with same Prod_ID) will always be 1 but may not always be first. The order of the Lang_ID could be 3,1,2 for example. – user1515104 Jul 11 '12 at 06:51
@user1515104 I added a subquery for sorting the table by Lang_ID first. – speakr Jul 11 '12 at 07:33

condition update cells within the same column

3 Answers3

If Lang_ID=1 is always the "first"

edit: If Lang_ID=1 may not be the "first"

Demo at sqlfiddle