Numbering items according to grouping element in column

Question

This is my table:

Item1   | Item2     | Item3     | Element   | ItemNumber
==============================================================
rock    | n         | roll      | r         | 1
rock    | n         | roll      | o         | 0
rock    | n         | roll      | c         | 0
rock    | n         | roll      | k         | 1
rock    | n         | roll      | n         | 2
rock    | n         | roll      | r         | 3
rock    | n         | roll      | o         | 0
rock    | n         | roll      | l         | 0
rock    | n         | roll      | l         | 3
a       | tiny      | rock      | a         | 1
a       | tiny      | rock      | t         | 2
a       | tiny      | rock      | i         | 0
a       | tiny      | rock      | n         | 0
a       | tiny      | rock      | y         | 2
a       | tiny      | rock      | r         | 3
a       | tiny      | rock      | o         | 0
a       | tiny      | rock      | c         | 0
a       | tiny      | rock      | k         | 3

I would like to update the column ItemNumber to have all elements correspond to the item number it belongs to according to the value in the column Element. Basically, i just want to add the correct missing values to ItemNumber where currently the value is 0. There are over 600.000 rows in my table with varying amount of elements.

The final table should look like this:

Item1   | Item2     | Item3     | Element   | ItemNumber
==============================================================
rock    | n         | roll      | r         | 1
rock    | n         | roll      | o         | 1
rock    | n         | roll      | c         | 1
rock    | n         | roll      | k         | 1
rock    | n         | roll      | n         | 2
rock    | n         | roll      | r         | 3
rock    | n         | roll      | o         | 3
rock    | n         | roll      | l         | 3
rock    | n         | roll      | l         | 3
a       | tiny      | rock      | a         | 1
a       | tiny      | rock      | t         | 2
a       | tiny      | rock      | i         | 2
a       | tiny      | rock      | n         | 2
a       | tiny      | rock      | y         | 2
a       | tiny      | rock      | r         | 3
a       | tiny      | rock      | o         | 3
a       | tiny      | rock      | c         | 3
a       | tiny      | rock      | k         | 3

How can I do this with MySQL?

Answer 1

What you need is a column, that uniquely identifies each row. It's always a good idea to have such a column (a.k.a. primary key).

Another column of use would be a column that determines the sort order of the rows. In other words, a column that determines that the rows with elements r,o,l and l should be listed after the rows containing r,o,c and k when you select them. In a possible solution I've used an auto_increment column for this.

CREATE TABLE t
    (id int auto_increment primary key, `Item1` varchar(4), `Item2` varchar(4), `Item3` varchar(4), `Element` varchar(1), `ItemNumber` int)
;

INSERT INTO t
    (`Item1`, `Item2`, `Item3`, `Element`, `ItemNumber`)
VALUES
    ('rock', 'n', 'roll', 'r', 1),
    ('rock', 'n', 'roll', 'o', 0),
    ('rock', 'n', 'roll', 'c', 0),
    ('rock', 'n', 'roll', 'k', 1),
    ('rock', 'n', 'roll', 'n', 2),
    ('rock', 'n', 'roll', 'r', 3),
    ('rock', 'n', 'roll', 'o', 0),
    ('rock', 'n', 'roll', 'l', 0),
    ('rock', 'n', 'roll', 'l', 3),
    ('a', 'tiny', 'rock', 'a', 1),
    ('a', 'tiny', 'rock', 't', 2),
    ('a', 'tiny', 'rock', 'i', 0),
    ('a', 'tiny', 'rock', 'n', 0),
    ('a', 'tiny', 'rock', 'y', 2),
    ('a', 'tiny', 'rock', 'r', 3),
    ('a', 'tiny', 'rock', 'o', 0),
    ('a', 'tiny', 'rock', 'c', 0),
    ('a', 'tiny', 'rock', 'k', 3)
;

UPDATE t
JOIN (
  SELECT
  t.*
  , @v := IF(ItemNumber != 0, ItemNumber, @v) as new_itemnumber
  FROM
  t
  , (SELECT @v:=0) var_init_subquery
  ORDER BY id
  ) sq USING (id)
SET t.ItemNumber = sq.new_itemnumber;

• see it working live in an sqlfiddle here

Answer 2

Something like this for every Item (1, 2, 3) of each phrase (rock n roll, a tiny rock):

UPDATE table_name
SET ItemNumber = 1
WHERE ItemNumber = 0 AND Item1 LIKE CONCAT('%', Element, '%') AND Item1 = 'rock';

The only problem will be tuples with Element = 'r' and Element = 'o' because they match with both 'rock' and 'roll'. This will be complicated because tuples are supposed to be unordered, so there would be no way to distinguish between an ItemNumber = 1 and an ItemNumber = 3. I would suggest redesigning your table.