レコードなしは挿入、レコードありは更新する方法

該当レコードなしのときは挿入、該当レコードがあるときは更新する方法を教えてください

データベース初心者です

該当レコードなしのときは挿入(INSERT)、該当レコードがあるときは更新(UPDATE)する方法を教えてください。


MySQL 5.0.67

レコードは下記のようになります。

desc table;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| val1 | smallint(6) | NO | PRI | 0 | |
| val2 | smallint(6) | NO | PRI | 0 | |
| val3 | smallint(6) | NO | PRI | 0 | |
| date_y | char(4) | NO | PRI | | |
| date_m | char(2) | NO | PRI | | |
| pre | bigint(19) | NO | | 0 | |
| now | bigint(19) | NO | | 0 | |
+----------+----------------------+------+-----+---------+-------+


下記のSQL文で更新を行いました。


UPDATE table as c,
(
SELECT val1, val2, val3, now
FROM table
WHERE date_y='2010' and date_m='10'
) as n
SET
c.pre=n.now,
c.now=n.now
WHERE c.val1=n.val1 and c.val2=n.val2 and c.val3=n.val3 and c.date_y='2010' and c.date_m='11';

このSQL文をINSERT～ON DUPLICATE KEY UPDATEにした場合、どのようなSQL文になるのかをご教授頂ければ幸いです。
一応、下記URLを参照したのだけど、うまくSQL文が書けませんでした。

12.2.4.3. INSERT ... ON DUPLICATE KEY UPDATE 構文
http://dev.mysql.com/doc/refman/5.1/ja/insert-on …

以上、宜しくお願い致します。

No.1 回答者： askaaska 回答日時： 2010/11/30 12:17

REPLACE文を使ってはどお？

この回答への補足

ご回答ありがとうございます。

> (1)REPLACEを使う
> (2)プライマリをつかって削除して、その後新規INSERTで投入をする。
> (3)プライマリをつかってIGNOREでINSERTして、その後UPDATEで更新をかける。

についてですが、(1)、(2)はできれば、使用したくないですね。
実際のレコードは、他にも多数のカラムが存在していて、削除すると、再度レコードの作り直しになりますので。

それと(3)についてですが、調べさせて頂きました。
http://dev.mysql.com/doc/refman/4.1/ja/insert.html
該当のレコードが無い場合は挿入で、該当のレコードがある場合は、無視され、挿入されないとなっておりました。
これも、挿入されない場合、UPDATEを呼び出さなければならず、ちょっと手間が掛かるので控えたいのです。

やはり１文で済ませたいので「ON DUPLICATE KEY UPDATE」を使用したいです。
ただ、マニュアルを読んでもコードの書き方が分からず、文法エラーとなってしまうので、
大変申し訳ありませんが、教えて頂けるとありがたいです。

補足日時：2010/11/30 13:34

この回答へのお礼

askaaskaさん

ご回答頂きまして、ありがとうございます。

お礼日時：2010/12/02 17:27

No.2 回答者： yambejp 回答日時： 2010/11/30 12:36

ロジック的には以下のどれか

(1)REPLACEを使う
(2)プライマリをつかって削除して、その後新規INSERTで投入をする。
(3)プライマリをつかってIGNOREでINSERTして、その後UPDATEで更新をかける。

実は(1)と(2)はロジック的には同じ感じです
文書的には(1)が簡単ですが、SQLの処理としては(3)が効率がいいです。

この回答へのお礼

yambejpさん

ご回答頂きまして、ありがとうございます。

お礼日時：2010/12/02 17:28

No.3 回答者： yambejp 回答日時： 2010/11/30 15:08

>ON DUPLICATE KEY UPDATE

ご提示のURLにきちんと例文も書かれているみたいですけどね・・・
ようはプライマリかユニークのカラムをキーにして競合するとき
UPDATEするわけです。

参考までにこんな感じです。

create table tbl(id int not null,data1 varchar(10),data2 varchar(10),primary key(id));
insert into tbl value(1,'aaa','bbb') on duplicate key update data1='aaa',data2='bbb';
insert into tbl value(2,'ccc','ddd') on duplicate key update data1='ccc',data2='ddd';
insert into tbl value(3,'eee','fff') on duplicate key update data1='eee',data2='fff';
insert into tbl value(2,'xxx','yyy') on duplicate key update data1='xxx',data2='yyy';

そもそもUPDATEとINSERTは性質が違うものなので、別々に処理する方がいいと
思うんですけどね

この回答への補足

yambejpさん

ご回答頂きまして、誠にありがとうございます。

ただ、こちらの質問の書き方が悪かったようで、申し訳ありません。
私が知りたいのは下記SQL文をどうやって「INSERT～ON DUPLICATE KEY UPDATE」を使用したSQL文になるのかが知りたいです。
※サンプルを参考にいろいろ記述を変えたけど、私が未熟なばかり、文法エラーが出るんです。。。

UPDATE table as c,
(
SELECT val1, val2, val3, now
FROM table
WHERE date_y='2010' and date_m='10'
) as n
SET
c.pre=n.now,
c.now=n.now
WHERE c.val1=n.val1 and c.val2=n.val2 and c.val3=n.val3 and c.date_y='2010' and c.date_m='11';


コレだと文法エラーになる。。。
↓
INSERT INTO table as c,
(
SELECT val1, val2, val3, now
FROM table
WHERE date_y='2010' and date_m='10'
) as n
VALUE (n.kukan_cd, n.updown, n.honsen, n.nowtupan)
ON DUPLICATE KEY UPDATE
c.pre=n.now,
c.now=n.now
WHERE c.val1=n.val1 and c.val2=n.val2 and c.val3=n.val3 and c.date_y='2010' and c.date_m='11';

エラーメッセージ：
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as c,(SELECT val1, val2, val3, now FROM t' at line 1


> そもそもUPDATEとINSERTは性質が違うものなので、別々に処理する方がいいと思うんですけどね
ごめんなさい。。。次回、コードを作成する際はご参考にさせて頂きます。

補足日時：2010/11/30 16:35

この回答へのお礼

yambejpさん

ご回答頂きまして、ありがとうございます。

お礼日時：2010/12/02 17:28

No.4 回答者： nora1962 回答日時： 2010/12/02 05:23

一応

INSERT INTO `table` ( val1, val2, val3, date_y, date_m, now, pre )
SELECT val1, val2, val3, date_y, '11', now, pre
FROM `table`
WHERE date_y='2010' and date_m='10'
ON DUPLICATE KEY UPDATE
pre=values(now),now=values(now);

でどうでしょう。
ただ、このSQLでは年の繰り上がりなどの場合の処理が面倒ですね。
後、一月１レコードという設計も正規化という観点から言えばかなり問題があるように思えます。

この回答への補足

nora1962さん

何度もお世話になり、恐縮致します。
また、SQL文を教えて頂き誠にありがとうございます。

> ただ、このSQLでは年の繰り上がりなどの場合の処理が面倒ですね。
その辺はプログラム上でカバーします。こんな感じで。。。

pre_year = now_year;
pre_month = now_month - 1;
if(pre_month == 0){// 該当月が1月なら
pre_y--;// 該当年を-1年
pre_m = 12;// 該当月を12月
}
※C/C++言語です

> 後、一月１レコードという設計も正規化という観点から言えばかなり問題があるように思えます。
ごめんなさい。既存のデータベースを使用しているため、変更ができないんです。。。
また、運用上、本テーブルは１日に１～２回程度しか更新されないので、さほど効率は重視されていないようです。
ただ、新規に開発を行う際はご参考にさせて頂きます。


で、nora1962さんの記載したSQL文を確認させて頂きました。

ただ、やはりこちらでも、私の端折った質問が悪かったですね。
下記URLと同じく、実際のテーブルは以下のようになります。
http://oshiete.goo.ne.jp/qa/6353578.html

pre:前月のval1,val2,val3が同じnowの値が保存されます。
now:本月のpre+day01～day31までの合計値が保存されます。

desc table;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| val1 | smallint(6) | NO | PRI | 0 | |
| val2 | smallint(6) | NO | PRI | 0 | |
| val3 | smallint(6) | NO | PRI | 0 | |
| date_y | char(4) | NO | PRI | | |
| date_m | char(2) | NO | PRI | | |
| pre | bigint(19) | NO | | 0 | |
| now | bigint(19) | NO | | 0 | |
| day01 | smallint(6) unsigned | NO | | 0 | |
| day02 | smallint(6) unsigned | NO | | 0 | |
～～～　途中省略　～～～
| day30 | smallint(6) unsigned | NO | | 0 | |
| day31 | smallint(6) unsigned | NO | | 0 | |
+----------+----------------------+------+-----+---------+-------+

nora1962さんの記載されたSQL文ですと、nowがpreと同じ値になります。
nowには本月(本レコード)のpre+day01～day31までの合計値を設定(更新)したいと思っております。

以上、何度もご質問をして申し訳ありませんが、宜しくお願い致します。

補足日時：2010/12/02 16:05

この回答へのお礼

# 更に追記させて頂きます

ちなみに以下のSQL文だと文法エラーとなります。
INSERT INTO `table` ( val1, val2, val3, date_y, date_m, now, pre )
SELECT val1, val2, val3, date_y, '11', now, pre
FROM `table`
WHERE date_y='2010' and date_m='10'
ON DUPLICATE KEY UPDATE
pre=values(now),now=values(now+day01+day02+day03～途中省略～+day29+day30+day31);

お礼日時：2010/12/02 17:25

No.5 回答者： nora1962 回答日時： 2010/12/02 18:10

更新部分の

> pre=values(now),now=values(now+day01+day02+day03～途中省略～+day29+day30+day31)
を
pre=values(now),now=values(now)+values(day01)+values(day02)+values(day03)～途中省略～+values(day31)
に変えるとどうなりますか。

この回答への補足

nora1962さん

何度も教えて頂き、本当にありがとうございます。
ただ、残念ながら、やはりうまくいきませんでした。

こちらで確認した内容をご報告させて頂きます。

◆その１
INSERT INTO `table` ( val1, val2, val3, date_y, date_m, now, pre )
SELECT val1, val2, val3, date_y, '11', now, pre
FROM `table`
WHERE date_y='2010' and date_m='10'
ON DUPLICATE KEY UPDATE
pre=values(now),now=values(now)+values(day01)+values(day02)+values(day03)～途中省略～+values(day29)+values(day30)+values(day31);
文法上はこれで問題ないようです。教えて頂きありがとうございます。
ただ、nowに設定された値がnowでした。
期待していたのはnow+day01+day02～なのですが。。。

◆その２
INSERT INTO `table` ( val1, val2, val3, date_y, date_m, now, pre )
SELECT val1, val2, val3, date_y, '11', now, pre
FROM `table`
WHERE date_y='2010' and date_m='10'
ON DUPLICATE KEY UPDATE
pre=values(now),now=values(day01);
だと、nowに0が設定されます。
もちろん、dayに値が設定されていることは確認しております。

◆その３
INSERT INTO `table` ( val1, val2, val3, date_y, date_m, now, pre )
(
SELECT val1, val2, val3, date_y, '11', now, pre
FROM `table`
WHERE date_y='2010' and date_m='10'
) as n
ON DUPLICATE KEY UPDATE
pre=values(now),now=values(now)+values(n.day01)+～途中省略～;
as句を使用しようと試みましたが。。。
これだと、文法エラーになります。
エラー内容は
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as n ON DUPLICATE KEY UPDATE pre=values(now),now=values(n.day01' at line 6

当然、こちらでも調査させて頂きますが、また何か方法がありましたら、教えて頂ければ嬉しいです。

補足日時：2010/12/03 10:40

この回答へのお礼

nora1962さん

お忙しいとは思いますが、
私のような初心者に丁寧に何度もご回答頂き本当にありがとうございます。

大変、感謝しております。

お礼日時：2010/12/03 10:42