What databases could run the following SQL?

Question

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.

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` < 1);

Here are the specific features I'm relying on (as I can think of them):

1. Update queries.
2. Joins in update queries.
3. Aggregate functions in non-explicitly-grouped queries.
4. WHERE...IN condition.

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 the readers-writers problem.

This query assumes there exists a table locks with two fields: key (varchar) and value (int). It further assumes that the table contains a row such that key="key1". Then it tries to increment the value for "key1". It only does so if for every key in the list ("key2","key3"), the associated value is 0 (the WHERE condition for l2 is an approximation that assumes value 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.

So, here are the additional conditions not discernable from the query itself:

1. Assumes that in a multi-threaded environment, a copy of this query will never be interleaved with another copy.
2. Processing the query must return whether any values were affected.

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."

Based on the responses, this query should work better across all systems:

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 < 1);

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).

Answer 1

This exact syntax would only work in MySQL.

It's an ugly workaround for this construct:

UPDATE  locks
SET     value = 1
WHERE   key = 'key1'
        AND NOT EXISTS
        (
        SELECT  NULL
        FROM    locks li
        WHERE   li.key IN ('key2', 'key3')
                AND li.value > 0
        )

which works in all systems except MySQL, because the latter does not allow subqueries on the target table in UPDATE or DELETE statements.