title: How to limit event type to one per day
body: <p>I have a query like so.. it's not working, though, I'm getting a syntax error on the WHERE NOT EXISTS bit.  However I'm not sure it would work the way I'd want it to anyway.. What I want to do is make it so there can only be one 'B' in any day, and it will be the first instance.. but I'm not sure if the below code worked, that anything would be 'B' until the very end anyway.</p>
  
  <pre><code>UPDATE FoodIntake
   SET MealTypeCode = CASE
  
  WHEN substr(DateTime, -8)
   BETWEEN (SELECT BreakfastStart FROM Patient WHERE PatientId = :PatientId)
   AND (SELECT LunchStart FROM Patient WHERE PatientId = :PatientId)
   AND CarbAmount &gt;= 25
   AND MealTypeCode IS NULL
   WHERE NOT EXISTS (
      SELECT * FROM FoodIntake as Old 
      WHERE MealTypeCode = 'B' 
      AND substr(Old.DateTime, 10) = substr(FoodIntake.DateTime, 10)
  )
   THEN 'B'
  END
  </code></pre>
  
  <p>The other option, that I'm not sure how to approach would be to tag everything that applies as B, then do a second query to find any time there are 2 Bs in one day and rename the second one as something else.</p>
  
  <p>I have a query I tried for that but I know it's got some major problems.. there's some syntax errors I can't quite figure out, but I think it at least gets across what I want to do.</p>
  
  <pre><code>UPDATE FoodIntake
   SET MealTypeCode = 'U'
  WHERE MealTypeCode = 'B'
  AND count(SELECT 1 FROM FoodIntake as Old WHERE MealTypeCode ='B' AND substr(Old.DateTime, 10) = substr(FoodIntake.DateTime, 10) ) &gt; 1
  AND FoodIntake.DateTime &gt; (SELECT DateTime FROM FoodIntake as Old WHERE MealTypeCode ='B' AND substr(Old.DateTime, 10) = substr(FoodIntake.DateTime, 10) ORDER By DateTime ASC LIMIT = count-1)
  </code></pre>