Processing of SQL query in cycle (storage procedure)



  • There's 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
    

    This results in such data:

    введите сюда описание изображения

    How can each of the records in this sample be given a number in a field value, from one to lines in the sample? For example, the first entry with id = 59517 in the field value is 1, the second entry with id = 72821 in the field receives value 2, etc., those at the height of id, the field of value according to order between 1 and 31 (as in this case).



  • So you need a sample or update the value in the custom_value(UPDATE)? If it's just a sample, you can number it:

      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 the value needs to be updated, 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
    

    That's supposed to be right. But you can read more about syntaxis. http://www.mysqltutorial.org/mysql-update-join/




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2