sql
1 UPDATE stock AS dist
2 INNER JOIN stock AS org ON dist . code = org . code
3 AND dist . date = ADDDATE ( org . date , 1 )
4 SET dist . hajimene = org . owarine ,
5 dist . takane = org . owarine ,
6 dist . yasune = org . owarine ,
7 dist . owarine = org . owarine
8 WHERE dist . dekidaka = 0 ;
実行結果
sql
1 mysql > CREATE TABLE stock (
2 - > code varchar ( 8 ) ,
3 - > hajimene int ,
4 - > takane int ,
5 - > yasune int ,
6 - > owarine int ,
7 - > dekidaka int ,
8 - > date_no int ,
9 - > date date
10 - > ) ;
11 Query OK , 0 rows affected ( 0.03 sec )
12
13 mysql > INSERT INTO stock VALUES
14 - > ( '1101-T' , 1800 , 1802 , 1760 , 1777 , 1500 , 1 , '2016-11-09' ) ,
15 - > ( '1101-T' , 1810 , 1812 , 1790 , 1799 , 2500 , 2 , '2016-11-10' ) ,
16 - > ( '1101-T' , 1840 , 1842 , 1810 , 1789 , 3500 , 3 , '2016-11-11' ) ,
17 - > ( '1102-T' , 2800 , 2802 , 2760 , 2777 , 2500 , 1 , '2016-11-09' ) ,
18 - > ( '1102-T' , 0 , 0 , 0 , 0 , 0 , 2 , '2016-11-10' ) ,
19 - > ( '1102-T' , 2840 , 2842 , 2740 , 2747 , 4500 , 3 , '2016-11-11' ) ;
20 Query OK , 6 rows affected ( 0.00 sec )
21 Records: 6 Duplicates: 0 Warnings : 0
22
23 mysql > SELECT * FROM stock ;
24 + --------+----------+--------+--------+---------+----------+---------+------------+
25 | code | hajimene | takane | yasune | owarine | dekidaka | date_no | date |
26 + --------+----------+--------+--------+---------+----------+---------+------------+
27 | 1101 - T | 1800 | 1802 | 1760 | 1777 | 1500 | 1 | 2016 - 11 - 09 |
28 | 1101 - T | 1810 | 1812 | 1790 | 1799 | 2500 | 2 | 2016 - 11 - 10 |
29 | 1101 - T | 1840 | 1842 | 1810 | 1789 | 3500 | 3 | 2016 - 11 - 11 |
30 | 1102 - T | 2800 | 2802 | 2760 | 2777 | 2500 | 1 | 2016 - 11 - 09 |
31 | 1102 - T | 0 | 0 | 0 | 0 | 0 | 2 | 2016 - 11 - 10 |
32 | 1102 - T | 2840 | 2842 | 2740 | 2747 | 4500 | 3 | 2016 - 11 - 11 |
33 + --------+----------+--------+--------+---------+----------+---------+------------+
34 6 rows in set ( 0.00 sec )
35
36 mysql > UPDATE stock AS dist
37 - > INNER JOIN stock AS org ON dist . code = org . code
38 - > AND dist . date = ADDDATE ( org . date , 1 )
39 - > SET dist . hajimene = org . owarine ,
40 - > dist . takane = org . owarine ,
41 - > dist . yasune = org . owarine ,
42 - > dist . owarine = org . owarine
43 - > WHERE dist . dekidaka = 0 ;
44 Query OK , 1 row affected ( 0.00 sec )
45 Rows matched : 1 Changed: 1 Warnings : 0
46
47 mysql > SELECT * FROM stock ;
48 + --------+----------+--------+--------+---------+----------+---------+------------+
49 | code | hajimene | takane | yasune | owarine | dekidaka | date_no | date |
50 + --------+----------+--------+--------+---------+----------+---------+------------+
51 | 1101 - T | 1800 | 1802 | 1760 | 1777 | 1500 | 1 | 2016 - 11 - 09 |
52 | 1101 - T | 1810 | 1812 | 1790 | 1799 | 2500 | 2 | 2016 - 11 - 10 |
53 | 1101 - T | 1840 | 1842 | 1810 | 1789 | 3500 | 3 | 2016 - 11 - 11 |
54 | 1102 - T | 2800 | 2802 | 2760 | 2777 | 2500 | 1 | 2016 - 11 - 09 |
55 | 1102 - T | 2777 | 2777 | 2777 | 2777 | 0 | 2 | 2016 - 11 - 10 |
56 | 1102 - T | 2840 | 2842 | 2740 | 2747 | 4500 | 3 | 2016 - 11 - 11 |
57 + --------+----------+--------+--------+---------+----------+---------+------------+
58 6 rows in set ( 0.00 sec )
ただし、このSQLでは「出来高ゼロ」の日が2日以上 連続している場合、最初の日の値しか更新できません。
どういうことかと言うと、例えばcode = 1101-T
のデータが以下のようになっていた場合、
sql
1 + --------+----------+--------+--------+---------+----------+---------+------------+
2 | code | hajimene | takane | yasune | owarine | dekidaka | date_no | date |
3 + --------+----------+--------+--------+---------+----------+---------+------------+
4 | 1101 - T | 1840 | 1842 | 1810 | 1789 | 3500 | 3 | 2016 - 11 - 11 |
5 | 1101 - T | 0 | 0 | 0 | 0 | 0 | 4 | 2016 - 11 - 12 |
6 | 1101 - T | 0 | 0 | 0 | 0 | 0 | 5 | 2016 - 11 - 13 |
7 + --------+----------+--------+--------+---------+----------+---------+------------+
上記SQLの実行後は以下のようにしかならない、ということです。
sql
1 + --------+----------+--------+--------+---------+----------+---------+------------+
2 | code | hajimene | takane | yasune | owarine | dekidaka | date_no | date |
3 + --------+----------+--------+--------+---------+----------+---------+------------+
4 | 1101 - T | 1840 | 1842 | 1810 | 1789 | 3500 | 3 | 2016 - 11 - 11 |
5 | 1101 - T | 1789 | 1789 | 1789 | 1789 | 0 | 4 | 2016 - 11 - 12 |
6 | 1101 - T | 0 | 0 | 0 | 0 | 0 | 5 | 2016 - 11 - 13 |
7 + --------+----------+--------+--------+---------+----------+---------+------------+
日次のバッチ処理で当日分(あるいは前日分)のデータだけを更新したいなら、これで十分です。
もし、そうでないのであれば、上記SQLを
「更新された行数(※)が0行になるまで繰り返し実行する」
などの対処が必要になります。
※SQL実行後に表示されるRows matched: 1 Changed: 1 Warnings: 0
のChanged: n
という部分
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/11/13 19:13