mysql join with sub-query

Question

This is my schema:

mysql> describe stocks;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| symbol    | varchar(32) | NO   |     | NULL    |                |
| date      | datetime    | NO   |     | NULL    |                |
| value     | float(10,3) | NO   |     | NULL    |                |
| contracts | int(8)      | NO   |     | NULL    |                |
| open      | float(10,3) | NO   |     | NULL    |                |
| close     | float(10,3) | NO   |     | NULL    |                |
| high      | float(10,3) | NO   |     | NULL    |                |
| low       | float(10,3) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
9 rows in set (0.03 sec)

I added the column open and low and I want to fill up with the data inside the table.

These values open/close are referenced to each day. (so the relative max/min id of each day should give me the correct value). So my first insight is get the list of date and then left join with the table:

SELECT DISTINCT(DATE(date)) as date FROM stocks

but I'm stuck because I can't get the max/min ID or the the first/last value. Thanks

Answer 1

Solved!

mysql> UPDATE stocks s JOIN
    -> (SELECT k.date, k.value as v1, y.value as v2 FROM (SELECT x.date, x.min_id, x.max_id, stocks.value FROM (SELECT DATE(date) as date,min(id) as min_id,max(id) as max_id  FROM stocks group by DATE(date)) AS x LEFT JOIN stocks ON x.min_id = stocks.id) AS k LEFT JOIN stocks y ON k.max_id = y.id) sd
    -> ON DATE(s.date)  = sd.date
    -> SET s.open = sd.v1, s.close = sd.v2;
Query OK, 995872 rows affected (1 min 50.38 sec)
Rows matched: 995872  Changed: 995872  Warnings: 0

Answer 2

You will get day wise min and max ids from below query

 SELECT DATE_FORMAT(date, "%d/%m/%Y"),min(id) as min_id,max(id) as max_id  FROM stocks group by DATE_FORMAT(date, "%d/%m/%Y")

But other requirement is not clear.