Mysql process data by groups

Question

I have a few groups of data. Each group has a some property field. For example:

_________________________
| id  | value | property |  
--------------------------
| 1    |  2    |    3    |
--------------------------
| 2    |  2    |    3    |
--------------------------
| 3    |  2    |    3    |
--------------------------
| 4    |  2    |    4    |
-------------------------
| 5    |  2    |    4    |
--------------------------
| 6    |  2    |    4    |
--------------------------

How can I update two strings ordered by id ASC with property = 3, and 2 strings ordered by id ASC with property = 4 by one query?

I want to update 2 of 3 rows with property = 3 and update 2 of 3 rows with property = 4. For example: rows with id 1 and 2, and rows with id 4 and 5
i.e. i want update groups of data with different conditions by one query

Answer 1

You can do it using calculated rank field, e.g. -

SELECT p1.*, COUNT(*) rank FROM properties p1
  LEFT JOIN properties p2
    ON p2.property = p1.property AND p2.id <= p1.id
  GROUP BY p1.property, p1.id

This query will return dataset with row-number by property:

+------+-------+----------+------+
| id   | value | property | rank |
+------+-------+----------+------+
|    1 |     2 |        3 |    1 |
|    2 |     2 |        3 |    2 |
|    3 |     2 |        3 |    3 |
|    4 |     2 |        4 |    1 |
|    5 |     2 |        4 |    2 |
|    6 |     2 |        4 |    3 |
+------+-------+----------+------+

Then you should update records with rank < 3:

UPDATE properties p
  JOIN (SELECT p1.*, COUNT(*) rank FROM properties p1
          LEFT JOIN properties p2
            ON p2.property = p1.property AND p2.id <= p1.id
        GROUP BY p1.property, p1.id) r
  ON p.id = r.id
  SET p.value = 100 -- set new value here
  WHERE r.rank < 3

Answer 2

Here's the solution, and see discussion following:

update 
  t,
  (select GROUP_CONCAT(ids) as matching_ids from (
    select 
      SUBSTRING_INDEX(GROUP_CONCAT(id order by id), ',', 2) AS ids
    from
      t
    where
      property in (3,4)
    group by
      property
    ) s1
  ) s2
set value=12345
where 
  FIND_IN_SET(id, matching_ids) > 0
;

To illustrate, and assuming your table is called t, and the initial state is:

[email protected]> select * from t;
+----+-------+----------+
| id | value | property |
+----+-------+----------+
|  1 |     2 |        3 |
|  2 |     2 |        3 |
|  3 |     2 |        3 |
|  4 |     2 |        4 |
|  5 |     2 |        4 |
|  6 |     2 |        4 |
+----+-------+----------+

The result of running this query is:

[email protected]> select * from t;
+----+-------+----------+
| id | value | property |
+----+-------+----------+
|  1 | 12345 |        3 |
|  2 | 12345 |        3 |
|  3 |     2 |        3 |
|  4 | 12345 |        4 |
|  5 | 12345 |        4 |
|  6 |     2 |        4 |
+----+-------+----------+

A brief explanation of the query:

I pick up the first two ids for each property using the SUBSTRING_INDEX(GROUP_CONCAT(id order by id), ',', 2) statement.

I combine the above using GROUP_CONCAT(ids) as matching_ids to get all valid ids.

Finally, I update all rows in the table where the id is within combined matching_ids text.

Notes:

• You should verify your group_concat_max_len variable is long enough. Default is 1024. You most probably want to have this in the millions, anyhow (regardless of my answer).

• The query is far from being optimal. It answers your question, but you can't have an optimal query here.

• You are most probably better off with a transaction containing two or three queries.

Good luck!