CODEWITHSUNDEEP
mysql
Boxuan
Boxuan

Reputation: 5157

Conditionally populate a new column

I have a table like this:

[C1]  [C2]  [C3]  [C4]
  a1    b1    c1  val1    -- group 1
  a1    b1    c1  val2    -- group 1
  a1    b1    c1  val3    -- group 1

  a2    b2    c2  val1    -- group 2
  a2    b2    c2  val2    -- group 2

  a3    b3    c3  val1    -- group 3
  a3    b3    c3  val2    -- group 3

I would like to generate values for a new column [c5], for each combination of [c1], [c2] and [c3]. The desired output would be something like:

[C1]  [C2]  [C3]  [C4]  [c5]
  a1    b1    c1  val1     1
  a1    b1    c1  val2     2
  a1    b1    c1  val3     3

  a2    b2    c2  val1     1
  a2    b2    c2  val2     2

  a3    b3    c3  val1     1
  a3    b3    c3  val2     2

For each group of [c1], [c2] and [c3], it will always be the same. You can consider the combination of the 3 columns as the primary key.

Upvotes: 1

Views: 101

Answers (1)

John Ruddell
John Ruddell

Reputation: 25842

this will take two queries. first query you need to alter the table to add the new column... I just made these varchar as thats what it looks like on my side...

SETUP:

CREATE TABLE my_table
(c1 VARCHAR(55) 
,c2 VARCHAR(55)
,c3 VARCHAR(55)
,c4 VARCHAR(55)
);

INSERT INTO my_table VALUES
  ('a1'  ,  'b1' ,   'c1',  'val1'),
  ('a1'  ,  'b1' ,   'c1',  'val2'),
  ('a1'  ,  'b1' ,   'c1',  'val3'),

  ('a2'  ,  'b2' ,   'c2',  'val1'),
  ('a2'  ,  'b2' ,   'c2',  'val2'),

  ('a3'  ,  'b3' ,   'c3',  'val1'),
  ('a3'  ,  'b3' ,   'c3',  'val2');

FIRST QUERY:

ALTER TABLE my_table
    ADD COLUMN c5 VARCHAR(55);

here we just added a new column.. called it c5.

SECOND QUERY:

UPDATE my_table mt,
(
    SELECT 
        c4, 
        c1, 
        IF(@A = c1, @B := @B + 1, @B := 1) AS new_col, 
        @A := c1 
    FROM my_table
    CROSS JOIN(SELECT @A := '', @B := 1)t
) temp 
SET mt.c5 = new_col 
    WHERE mt.c4 = temp.c4 
    AND mt.c1 = temp.c1;

here we do some calculations to do a count that increments for each type and resets when it reaches a new one. then update the table in the same query

THIRD QUERY:

SELECT * FROM my_table;

just a way to see the changes done.

DEMO

OUTPUT:

+---+---+---+-------+--+
|c1 |c2 |c3 |c4     |c5|
+---+---+---+-------+--+
|a1 |b1 |c1 |val1   |1 |
|a1 |b1 |c1 |val2   |2 |
|a1 |b1 |c1 |val3   |3 |

|a2 |b2 |c2 |val1   |1 |
|a2 |b2 |c2 |val2   |2 |

|a3 |b3 |c3 |val1   |1 |
|a3 |b3 |c3 |val2   |2 |
+---+---+---+-------+--+

BONUS:

just for fun you could also just run the query if you didn't want to make a new column in the table. like so:

SELECT c1, c2, c3, c4, new_col as c5 
FROM
(
    SELECT 
        *, 
        IF(@A = c1, @B := @B + 1, @B := 1) AS new_col, 
        @A := c1 
    FROM my_table
    CROSS JOIN(SELECT @A := '', @B := 1)t
) as _outer_

Upvotes: 3

Related Questions