There is a sample:

SELECT issues.id, issues.priority_id, issues.project_id, issues.subject, c.value, t.tag_id from issues INNER JOIN taggings t ON issues.id = t.taggable_id and t.tag_id = 623 INNER JOIN custom_values c ON c.customized_id = issues.id and c.custom_field_id = 105 WHERE issues.priority_id = 4 and issues.project_id = 66 and issues.status_id NOT IN ( 5, 6, 11, 9) ORDER BY issues.id

As a result, the following data is output:

enter image description here

How to make so that each record in this sample in the value field is substituted with a number, in order from 1 to the number of rows in the sample? Those. for example, the first entry with id = 59517 in the value field is set to 1, the second entry with id = 72821 in the value field is set to 2, etc., those are in increasing id, respectively, the value field is numbered in order from 1 to 31 ( as in this case).

  • Is the number necessary only in the sample? Or should it be updated in the database? - newman
  • @newman in the database update - S.Ivanov

2 answers 2

So do you need a sample or update the value in the custom_value (UPDATE) table? If just a sample, then you can re-number it like this:

  SELECT issues.id, issues.priority_id, issues.project_id, issues.subject, @I := @I+1 as value, t.tag_id from issues INNER JOIN taggings t ON issues.id = t.taggable_id and t.tag_id = 623 INNER JOIN custom_values c ON c.customized_id = issues.id and c.custom_field_id = 105 CROSS JOIN (SELECT @I := 0)I WHERE issues.priority_id = 4 and issues.project_id = 66 and issues.status_id NOT IN ( 5, 6, 11, 9) ORDER BY issues.id

If you need to update the value, for example:

 UPDATE custom_values INNER JOIN issues ON custom_values.customized_id = issues.id and custom_values.custom_field_id = 105 INNER JOIN taggings t ON issues.id = t.taggable_id and t.tag_id = 623 CROSS JOIN (SELECT @I := 0)I SET Value = @I := @I + 1 WHERE issues.priority_id = 4 and issues.project_id = 66 and issues.status_id NOT IN ( 5, 6, 11, 9) ORDER BY issues.id

It seems to be right. But you can read more about the syntax UPDATE + JOIN in MySQL here

    To update the values ​​in the database, go around all the rows in the selection with the Cursor . Create some @value variable that will increase by 1 with each new line. For each row obtained using FETCH, set the value of @value in the required field of the database record, which has the value of issues.id obtained from the selection string.