Using a DELETE w/ a JOIN and LIMITing the number of rows deleted.
I've come up with some interesting workarounds for missing features using @session variables and I'd like to share one with you today: DELETE ... JOIN ... LIMIT N;

okay, so we've got two tables, one with many duplicates and one with no duplicates.
We want to join the tables using 'id', but only want to delete N duplicates from t1.
MySQL's DELETE does not include LIMIT support when JOIN is used, so we need to work around
that using some fancy footwork:


mysql> select * from t1;
+------+------+
| id | abc |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+------+------+
8 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| id | xyz |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)

mysql> set @limit=2; set @rowCount=0;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE t1.*
-> FROM t1
-> JOIN t2 on (t1.id = t2.id)
-> WHERE t2.id = 1
-> AND CONCAT(t1.id, t1.abc, @rowCount +1)
-> = CONCAT(t1.id, t1.abc,
-> CASE @rowCount
-> WHEN @LIMIT THEN @rowCount:=NULL
-> ELSE @rowCount := @rowCount+1
-> END
-> );
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id | abc |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
+------+------+
6 rows in set (0.00 sec)

how it works:
When MySQL first evaluates the expression, @rowCount is zero. The CONCAT in the where clause concatenates all the values given together, so at first we get CONCAT(1, 1, 1) = CONCAT(1, 1, 1). The next time through, we get CONCAT(1, 1, 2) = CONCAT(1, 1, 2). At the third execution we get CONCAT(1, 1, 3) = CONCAT(1, 1, NULL), which does not evaluate true. At this time @rowCount is now NULL, so subsequent scanned rows evaluate to
CONCAT(1, 1, NULL) = CONCAT(1, 1, NULL), which can never evaluate true (NULL != NULL).

So, what interesting ways have you used @session variables to come up with solutions to intractable problems?