title: What databases could run the following SQL?
body: <p>I have constructed a query and I'm wondering if it would work on any database besides MySQL. I have never actually used another database so I'm not great with the differences.</p>
  
  <pre><code>UPDATE `locks` AS `l1`
    CROSS JOIN (SELECT SUM(`value`) AS `sum` FROM `locks`
                WHERE `key` IN ("key3","key2")) AS `l2`
  SET `l1`.`value` = `l1`.`value` + 1
  WHERE `l1`.`key` = "key1" AND (`l2`.`sum` &lt; 1);
  </code></pre>
  
  <p>Here are the specific features I'm relying on (as I can think of them):</p>
  
  <ol>
  <li><strike>Update queries.</strike></li>
  <li>Joins in update queries.</li>
  <li>Aggregate functions in non-explicitly-grouped queries.</li>
  <li>WHERE...IN condition.</li>
  </ol>
  
  <p>I'm sure people will be curious exactly what this does, and this may also include database features that might not be ubiquitous. This is an implementation of mutual exclusion using a database, intended for a web application. In my case I needed it because certain user actions cause tables to be dropped and recreated with different columns, and I want to avoid errors if other parts of the application try to insert data. The implementation, therefore, is specialized to solve <a href="http://en.wikipedia.org/wiki/Readers-writers_problem" rel="nofollow">the readers-writers problem</a>.</p>
  
  <p>This query assumes there exists a table <code>locks</code> with two fields: <code>key</code> (varchar) and <code>value</code> (int). It further assumes that the table contains a row such that <code>key</code>="key1". Then it tries to increment the <code>value</code> for "key1". It only does so if for every <code>key</code> in the list <code>("key2","key3")</code>, the associated <code>value</code> is 0 (the WHERE condition for <code>l2</code> is an approximation that assumes <code>value</code> is never negative). Therefore this query only "obtains a lock" if certain conditions are met, presumably in an atomic fashion. Then, the application checks if it received a lock by the return value of the query which presumably states how many rows were affected. If and only if no rows were affected, the application did not receive a lock.</p>
  
  <p>So, here are the additional conditions not discernable from the query itself:</p>
  
  <ol>
  <li>Assumes that in a multi-threaded environment, a copy of this query will never be interleaved with another copy.</li>
  <li>Processing the query must return whether any values were affected.</li>
  </ol>
  
  <p>As a secondary request, I would appreciate any resources on "standard SQL." I've heard about it but never been able to find any kind of definition, and I feel like I'm missing a lot of things when the MySQL documentation says "this feature is an extension of standard SQL."</p>
  
  <p>Based on the responses, this query should work better across all systems:</p>
  
  <pre><code>UPDATE locks AS l1
    CROSS JOIN (SELECT SUM(val) AS others FROM locks
                WHERE keyname IN ('key3','key2')) AS l2
  SET l1.val = l1.val + 1
  WHERE l1.keyname = 'key1' AND (l2.others &lt; 1);
  </code></pre>
  
  <p>Upvotes for everyone because of the good answers. The marked answer seeks to directly answer my question, even if just for one other DBMS, and even though there may be better solutions to my particular problem (or even the problem of cross-platform SQL in general).</p>