|
|
The call for papers for the MySQL Users
Conference and Expo 2009 is open. Proposals are accepted
until October 22, 2008. This post will tell you how to get your proposal accepted. First: READ the following posts. I mean it!
|
Sep
08
2008
May
14
2008
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 …