ON DUPLICATE KEY UPDATE

質問

3ヶ月の集計結果を、QWERTYのPKとかぶらないものだけインサートします。
ただし、PKとかぶったものの中でも、QWERTYのCNT1が0のレコードについては、
QWERTYのCNT1のみ集計結果をアップデートしようとしています。

しかし、「~」部分の「ON DUPLICATE KEY UPDATE・・・」を挿入した際に、
エラー(ERROR 1111 (HY000): Invalid use of group function)となってしまいます。

＜SQL文＞
INSERT IGNORE INTO QWERTY( PHOGE, J, MHOGE, SHOGE, CNT1, CNT2, CNT3, NDATE, YMO )
select TEST.PHOGE, TEST.J, 'ABCD', TEST.SHOGE, sum(TEST.m1cnt) AS M1, sum(TEST.m2cnt) AS M2, sum(TEST.m3cnt) AS M3, '2014-07-01' , TEST.YMO
from
(
select PHOGE, J, SHOGE, SUM(OD_CNT) AS m1cnt, 0 AS m2cnt, 0 AS m3cnt, YMO
from HUJIKO WHERE CARD = '1' AND (KHOGE = '1' OR KHOGE = '9') AND (ODATE LIKE'2014-07%')
group by PHOGE, J, YMO
UNION
select PHOGE, J, SHOGE, 0 AS m1cnt, SUM(OD_CNT) AS m2cnt, 0 AS m3cnt, YMO
from HUJIKO WHERE CARD = '1' AND (KHOGE = '1' OR KHOGE = '9') AND (ODATE LIKE'2014-08%')
group by PHOGE, J, YMO
UNION
select PHOGE, J, SHOGE, 0 AS m1cnt, 0 AS m3cnt, SUM(OD_CNT) AS m3cnt, YMO
from HUJIKO WHERE CARD = '1' AND (KHOGE = '1' OR KHOGE = '9') AND (ODATE LIKE'2014-09%')
group by PHOGE, J, YMO
) AS TEST
GROUP BY TEST.PHOGE, TEST.YMO, TEST.J
ON DUPLICATE KEY UPDATE CNT1=if(CNT1=0, TEST.M1 , CNT1);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

「ON DUPLICATE KEY UPDATE 」で実現不可能であれば、
他の方法を取りたいのですが、
なるべく一つのSQL文で実現可能な方法をとりたいと考えています。

ご教示の程よろしくお願いいたします。

回答No.2

yambejp

＞QWERTYテーブルは、5つのカラム(PHOGE,　MHOGE, J, YMO, NDATE)でユニーク

として、この5カラムのユニーク属性を利用するならON DUPLICATEで処理だし
それ以外でユニーク処理をしたいならテーブルの制限がつかえない以上
SQLを１文でやるのはそこそこ厳しい

そもそもPKってのがよくわからないのですが？

補足

PKは、プライマリキーです。

よろしくお願いします。

回答No.1

yambejp

おなじテーブルを再現できないのでなんともいえませんが
QWERTYテーブルのPHOGEにUNIQUE属性がついているということでよろしいですか？
UNIONしているくだりが無駄な気がしますのでこんなかんじでしょうか？

INSERT INTO QWERTY( PHOGE, J, MHOGE, SHOGE, CNT1, CNT2, CNT3, NDATE, YMO )
select PHOGE
, J
,'ABCD'
, SHOGE
, SUM(OD_CNT*(ODATE LIKE'2014-07%')) AS M1
, SUM(OD_CNT*(ODATE LIKE'2014-08%')) AS M2
, SUM(OD_CNT*(ODATE LIKE'2014-09%')) AS M3
, '2014-07-01'
, YMO
from HUJIKO WHERE CARD = '1'
group by PHOGE, J, YMO
ON DUPLICATE KEY UPDATE CNT1=if(CNT1=0, TEST.M1 , CNT1);

補足

＞おなじテーブルを再現できないのでなんともいえませんが
＞QWERTYテーブルのPHOGEにUNIQUE属性がついているということでよろしいですか？
→QWERTYテーブルは、5つのカラム(PHOGE,　MHOGE, J, YMO, NDATE)でユニークになっています。

＞UNIONしているくだりが無駄な気がしますのでこんなかんじでしょうか？
→はい提示の通り、そんなかんじです。
(具体的に言いますと、
HUJIKOテーブルから、7, 8, 9月それぞれの集計結果をUNIONで縦に出し、名前をTESTテーブルとします。
その後、「GROUP BY TEST.PHOGE, TEST.YMO, TEST.J」で横に(1レコードに)まとめている処理です。
今回聞きたい内容からすれば冗長ですね失礼しましたm(__)m
)

引き続き、よろしくお願いします。