Question 272420 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Populate columns to be the same when matching ID is found

If multiple entries have the same ID (tag_num), I would like the columns to be the same. For example, if one row may have a description and another one is null but both have the same ID(tag_num), I’d like the description to be copied to the null row as well.

Before:

--------------------------------------
| tag_num |  description  | category |
--------------------------------------
|    1    | Description A |     A    |
|    1    |               |          |
|    2    | Description B |     B    |
|    2    | Description B |     B    |
|    2    | Description B |          |
|    3    | Description C |          |
|    3    |               |          |
--------------------------------------

Expected output:

--------------------------------------
| tag_num |  description  | category |
--------------------------------------
|    1    | Description A |     A    |
|    1    | Description A |     A    |
|    2    | Description B |     B    |
|    2    | Description B |     B    |
|    2    | Description B |     B    |
|    3    | Description C |          |
|    3    | Description C |          |
--------------------------------------

This is the SQL I’ve written so far (for mySQL) but I’m stumped:

SELECT * FROM table
WHERE tag_num IN (
    SELECT tag_num
    FROM table
    GROUP BY tag_num
    -- update empty columns here where columns are empty
)
Tags:

2

Answers


  1. 0

    Assuming you’re using Mysql 8+ a windowed aggregate is a possible solution:

    select tag_num,
    Coalesce(description, Max(description) over(partition by tag_num)) description,
    Coalesce(category, Max(category) over(partition by tag_num)) category
from t;
    Login or Signup to reply.
  2. 0

    If analytic functions are available to you, use them (as Stu said; though, as all values should be just the same, perhaps you don’t have to check whether something is NULL or not):

    SQL> select tag_num,
  2    max(description) over (partition by tag_num) description,
  3    max(category) over (partition by tag_num) category
  4  from test
  5  order by tag_num;

   TAG_NUM DESCRIPTION  CATEGORY
---------- ------------ ------------
         1 desc A       A
         1 desc A       A
         2 desc B       B
         2 desc B       B
         2 desc B       B
         3 desc C
         3 desc C

7 rows selected.

    Alternatively, find target values in a subquery and join it to source data. This option isn’t as good as previous as you have to query the same table twice so performance might suffer if you deal with large tables; you won’t notice any difference on small tables, though.

    SQL> select a.tag_num, b.description, b.category
  2  from test a join (select tag_num,
  3                           max(description) description,
  4                           max(category)    category
  5                    from test
  6                    group by tag_num
  7                   ) b on a.tag_num = b.tag_num
  8  order by a.tag_num;

   TAG_NUM DESCRIPTION  CATEGORY
---------- ------------ ------------
         1 desc A       A
         1 desc A       A
         2 desc B       B
         2 desc B       B
         2 desc B       B
         3 desc C
         3 desc C

7 rows selected.

SQL>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search