title: Updating Order field in SQL
body: <p>We're required to add Ordering to our EventVenue table, which we've done by adding an int field to store the EventVenue's placing in the order. The ordering is based upon the parent Event's ID and the Event's VenueGrouping.</p>
  
  <p>If the VenueGrouping is false the EventVenues will be ordered sequentially. However, if true, the EventVenues for the same Event and derived from the same parent Event will have the same ordering. For example [assuming we've filtered by Event]:</p>
  
  <pre><code>EVID | VenueID | Not Grouped | Grouped
    1  |    1    |      1      |   1
    2  |    2    |      2      |   2
    3  |    2    |      3      |   2
    4  |    3    |      4      |   3
  </code></pre>
  
  <p>The problem is when the grouping is changed. I need some SQL to update the Event's Venue Ordering appropriately but am finding it tricky to get my head round it, especially when going from ungrouped to grouped without leaving gaps.</p>
  
  <p>I'd presume it needs to be in an if statement like below, but if a more efficient/DRY solution is suitable, all the better: </p>
  
  <pre><code>-- Changing from Ungrouped to Grouped
  IF @OldGrouping=0 AND @NewGrouping=1
     BEGIN
        UPDATE EventVenues SET Ordering=...
     END
  -- Changing from Grouped to Ungrouped
  ELSE IF @OldGrouping=1 AND @NewGrouping=0
     BEGIN
        UPDATE EventVenues SET Ordering=...
     END
  </code></pre>
  
  <p>Any help appreciated.</p>
  
  <p>As requested, more example data:
  Got annoyed at formatting so used a key for headers:
  A = EventVenueID, B = VenueID, C = Original Ordering, D = Updated Ordering</p>
  
  <h2>Updating from Ungrouped to Grouped</h2>
  
  <blockquote>
  <pre><code> A  | B | C | D
  101 | 1 | 4 | 3
  102 | 2 | 2 | 2
  103 | 2 | 3 | 2
  104 | 3 | 1 | 1
  </code></pre>
  </blockquote>
  
  <h2>Updating from Grouped to Ungrouped</h2>
  
  <blockquote>
  <pre><code> A  | B | C | D
  101 | 1 | 3 | 4
  102 | 2 | 2 | 2
  103 | 2 | 2 | 3
  104 | 3 | 1 | 1
  </code></pre>
  </blockquote>