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

Mysql – Update a column value based on others' grouped value?

Please help me with a query to update a column value based on others grouped value. Thanks in advance.
Hard to explain so here is an example as I want to update Group value in this table:
Column:

    [Id, Delivery, Food, Group]

Value:

    [1, Ups, Vege, null] 
    [2, DHL, Vege, null] 
    [3, Ups, Meat, null] 
    [4, Ups, Vege, null] 
    [5, DHL, Mushroom, null]
    [6, Fedex, Mushroom, null]
    [7, Ups, Mushroom, null]
    [8, Ups, Meat, null]

It’s like first group with Delivery then Group value will be incremental by grouped Food.

Expect to Group to be updated as:

    [1, Ups, Vege, 1] 
    [2, DHL, Vege, 1] 
    [3, Ups, Meat, 2] 
    [4, Ups, Vege, 1] 
    [5, DHL, Mushroom, 2]
    [6, Fedex, Mushroom, 1]
    [7, Ups, Mushroom, 3]
    [8, Ups, Meat, 2]

Seems mysql doesn’t support functions like DENSE_RANK(), so it’s quite inconvenient.

Tags:

2

Answers


  1. 0

    Please check my complete query

    create table tableName  (Id int identity(1,1), Delivery varchar(100), Food varchar(100), [Group] varchar(100))

insert into tableName (Delivery,Food) select   'Ups','Vege'  union all
   select'DHL','Vege'  union all
   select 'Ups','Meat'  union all
   select 'Ups','Vege'  union all
   select'DHL','Mushroom'  union all
  select 'Fedex','Mushroom'  union all
  select 'Ups','Mushroom'  union all
   select'Ups','Meat' 

  -- select * from tableName


 select id,Delivery,food,[group],   DENSE_RANK() OVER (ORDER BY CASE 
                                    WHEN Food = 'Vege' THEN 1 
                                    WHEN Food = 'Meat' THEN 2
                                    WHEN Food = 'Mushroom' THEN 3
                                    ELSE 4 -- Handle other cases if any
                                END) AS RowNumber
 from tableName 

select * from tableName A inner join  (   select id,    DENSE_RANK() OVER (ORDER BY CASE 
WHEN Food = 'Vege' THEN 1 
WHEN Food = 'Meat' THEN 2
WHEN Food = 'Mushroom' THEN 3
ELSE 4 -- Handle other cases if any
END) AS RowNumber   from tableName ) As B ON A.id=B.id

update A  set A.[Group]=B.RowNumber 
from tableName A  inner join  (   select id,    DENSE_RANK() OVER (ORDER BY CASE 
    WHEN Food = 'Vege' THEN 1 
    WHEN Food = 'Meat' THEN 2
    WHEN Food = 'Mushroom' THEN 3
    ELSE 4 -- Handle other cases if any
END) AS RowNumber   from tableName ) As B ON A.id=B.id

select * from  tableName
    Login or Signup to reply.
  2. 0

    SELECT id, Delivery, Food, DENSE_RANK() OVER (PARTITION BY Delivery ORDER BY Delivery desc,food asc) AS GroupValue
    FROM tb1 order by id

    select statement can be used to update table.
    the result wont be exact because in your expected order food is neither asc nor desc. if same output is needed then one more column needs to be added in which food order is to be specified. and the field need to be included in the above select statement in place of ‘food’

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

Back To Top
Search