Re: inserting + sorting

[laptop alias]

1.

DROP TABLE IF EXISTS sell_registry;

CREATE TABLE sell_registry(client_code VARCHAR(12) NOT NULL UNIQUE,sell_date DATE NOT NULL,PRIMARY KEY(client_code,sell_date));

INSERT INTO sell_registry VALUES ('Phill','2001-01-01'),('Ted','2001-01-02');

DROP TABLE IF EXISTS testing;

CREATE TABLE testing(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,client_code VARCHAR(12) NOT NULL,sell_date DATE NULL);

INSERT INTO testing VALUES (NULL,'Phill','2001-01-02'),(NULL,'Phill',NULL),(NULL,'Ed',NULL);

SELECT * FROM sell_registry;
+-------------+------------+
| client_code | sell_date  |
+-------------+------------+
| Phill       | 2001-01-01 |
| Ted         | 2001-01-02 |
+-------------+------------+

SELECT * FROM testing;
+----+-------------+------------+
| id | client_code | sell_date  |
+----+-------------+------------+
|  1 | Phill       | 2001-01-02 |
|  2 | Phill       | NULL       |
|  3 | Ed          | NULL       |
+----+-------------+------------+

UPDATE testing t JOIN sell_registry sr ON sr.client_code = t.client_code SET t.sell_date = sr.sell_date;

SELECT * FROM testing;
+----+-------------+------------+
| id | client_code | sell_date  |
+----+-------------+------------+
|  1 | Phill       | 2001-01-01 |
|  2 | Phill       | 2001-01-01 |
|  3 | Ed          | NULL       |
+----+-------------+------------+

2. E.g.

SELECT PERIOD_DIFF(DATE_FORMAT('2011-01-20 12:34:56','%Y%m'),DATE_FORMAT('2010-02-24 11:24:09','%Y%m'))x;