title: How can you update based on the results of an IF statement in query?
body: <p>As the question above says, I have an IF clause in my query. Now I want to do an UPDATE based on the results of the IF clause. Any ideas how I can do this?</p>
  
  <p>Here is my code:</p>
  
  <pre><code>string cmdstr = "UPDATE itemsordered i " +
                  "INNER JOIN" +
                      "(  SELECT itemsOrdered_quantity, itemsOrdered_ID,  " +
                              "CASE WHEN itemsOrdered_quantity = '" + quantityTxtBox.Text + "' THEN 'EQUAL' " +
                                   "WHEN itemsOrdered_quantity &lt; '" + quantityTxtBox.Text + "' THEN 'LESS' " +
                                   "WHEN itemsOrdered_quantity &gt; '" + quantityTxtBox.Text + "' THEN 'MORE'  " +
                          "END AS r " +
                          "FROM itemsordered " +
                      ") res ON i.itemsOrdered_ID = res.itemsOrdered_ID " +
                 "INNER JOIN stocksdb s ON s.stock_ID = i.stock_ID " +
                 "IF (res.r = 'EQUAL') " +
                 "BEGIN " +
                 "SET s.stock_quantity = (s.stock_quantity + i.itemsOrdered_quantity), " +
                     "s.stock_dateUpdated = '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "', " +
                     "i.itemsOrdered_status = 'RECEIVED', " +
                     "i.itemsOrdered_dateReceived = '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "' " +
                     "WHERE i.itemsOrdered_ID = '" + idTxtBox.Text + "' " +
                 "END " +
                 "IF (res.r = 'LESS') " +
                 "BEGIN " +
                 "SET s.stock_quantity = (s.stock_quantity + i.itemsOrdered_quantity), " +
                     "s.stock_dateUpdated = '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "', " +
                     "i.itemsOrdered_quantity = (i.itemsOrdered_quantity - " + quantityTxtBox.Text + "), " +
                     "i.itemsOrdered_dateReceived = '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "' " +
                     "WHERE i.itemsOrdered_ID = '" + idTxtBox.Text + "' " +
                 "END";
  cmd = new MySqlCommand(cmdstr, db.mycon);
  cmd.ExecuteNonQuery();
  
  MessageBox.Show("ITEM RESTOCKED!");
  </code></pre>
  
  <p>It returns an error on the <code>cmd.ExecuteNonQuery()</code> and says there is an error near the first<code>IF</code> clause.</p>