How to get the latest :q
Here you go, ansi!
How Do I Join Multiple Times Against the Same Table?
How Do I Update Multiple Records At The Same Time?
How Can I Update Multiple Records Simultaneously?
Getting an ERROR 1062 when trying to make a column into an auto-incrementing primary key?
select * is evil

delete_from_table_a_where_not_in_table_b.sql
find duplicated data into a table



	/* select n random records */

SELECT fields FROM table ORDER BY rand() LIMIT n;

	/* select from table1 where not in table2 or table 3 */

SELECT t1.id FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t1.id = t3.id
WHERE t2.id IS NULL AND t3.id IS NULL;


-------------------------------------------------------
 FINDING GAPS IN AN OTHERWISE SEQUENTIAL SERIES OF IDs
-------------------------------------------------------

-- create our test table
mysql> CREATE TABLE holes (id SMALLINT); INSERT INTO holes VALUES (1),(2),(3),(5),(6),(8),(9),(10),(11),(100),(101),(102),(104);
Query OK, 0 rows affected (0.00 sec)

Query OK, 13 rows affected (0.00 sec)
Records: 13  Duplicates: 0  Warnings: 0

-- check to make sure everything is there
mysql> select * from holes;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    5 |
|    6 |
|    8 |
|    9 |
|   10 |
|   11 |
|  100 |
|  101 |
|  102 |
|  104 |
+------+
13 rows in set (0.00 sec)

-- join the table to itself twice, finding any record without a corresponding record with a key of itself - 1
mysql> SELECT A.id FROM holes A LEFT JOIN holes B ON A.id = B.id + 1 LEFT JOIN holes C ON C.id = B.id WHERE B.id IS NULL;
+------+
| id   |
+------+
|    1 |
|    5 |
|    8 |
|  100 |
|  104 |
+------+
5 rows in set (0.00 sec)

-- note: this will always return the MIN(holes.id) value, even though there is no gap; ignore it!

patch a series of records with data from other records in the same table in mysql

mysql -u root scrubber -e"select concat('UPDATE custdata SET addr1 = \"', b.addr1, '\", addr2 = \"', b.addr2, '\", city = \"', b.city, '\", state = \"', b.state, '\", zip = \"', b.zip, '\", zip4 = \"', b.zip4, '\" WHERE custdata_id = ', a.custdata_id, ';') from custdata a join custdata b on b.custdata_id = a.custdata_id + 1503 where a.addr1 = ''" |grep -o 'UPDATE.*;'|mysql -u root scrubber