Q&A
例示を見ますと該当する全データ総とっかえのようですので
・不要行をDELTEで削除
・追加行をINSERTで追加
が一番早いと思いますが、あえてUPDATEする必要は何かあるのでしょうか。
目的：複数行の更新を一括で行いたい（トータルで20,000行程度）
MySQL（Ver 14.14）を使用しております。
基本的なupdate文を見ると、カラム名や値を指定してupdateしていますが、
複数行の場合、insert文のように（値１、値２、値３）という記述で複数行更新することは可能なのでしょうか？
色々なサイトをみてみましたが、量が多い場合のupdate文で効率よく記述する方法はありますか？
例）
|id|title|content|data
|:--|:--:|--:|
|1|りんご|赤い|2020/11/11
|2|れもん|すっぱい|2020/11/20
|3|もも|甘い|2020/11/30
このようなデータを下記のように値変更を、一括で行いたいです。
↓
|id|title|content|data
|:--|:--:|--:|
|1|ぶどう|むらさき|2020/10/11
|2|いちご|あかい|2020/12/20
|3|ばなな|黄色い|2020/10/30
参考にしたサイト
・https://www.atmarkit.co.jp/ait/articles/1210/23/news008_2.html
・https://teratail.com/questions/188661
case等を使うと、カラム指定と値指定を1回1回行わなければいけないので、
insert文のように、最初に変更したいカラム名を指定して、そのあとで値だけを指定したいのですが
そういった記述方法は可能なのでしょうか…？
調べても分からずでしたので、お力添えいただきたいです。よろしくお願いします。
コメントありがとうございます。
例が悪すぎました…。約2万行あるうちの1万行程度のものを更新したくてでして…
そうなるとどうするのが一番効率よく更新作業が行えるのかというところでつまづいております…
SQLの種類はなんでしょう？
知識不足で申し訳ありません。SQLの種類とは…どういうことでしょうか？
たとえばOracle、MySQL、PostgreSQL、SQL Server(MS)などが有名です
ありがとうございます。MySQLを使用しております。
>MySQLを使用しております。
タグに追加し、バージョンも質問本文に記載してください。
修正いたしました。ご指摘ありがとうございます。
「約2万行あるうちの1万行程度のものを更新したくてでして…」
この作業は頻繁にあるのでしょうか？
そうでないのであればその1万行を削除して、UPDATEしない内容でINSERTすればよろしいのかなと思います。
頻繁にあるのであれば全データの約半分が毎回更新とのことですとテーブル構造から見直して法がよいのかなと思います。
いえ！一度のみの作業となります。
削除して入れ直す方法も視野に入れておきます。
ありがとうございます！
回答3件
0
ベストアンサー
例を見る限りidを元に、その他のカラムをすべて更新する感じなので
deleteしてinsertするのが一番効率的だと思います。
投稿2020/09/16 03:30
総合スコア116835
一括で行うのは厳しそうですよね…ありがとうございます。
SQLの種類によっては書き方があります。
でも効率的かどうかは別問題です
//sample
create table tbl(id int primary key,title varchar(10), content varchar(10),data date);
insert into tbl values
(1,'りんご','赤い','2020-11-11'),
(2,'れもん','すっぱい','2020-11-20'),
(3,'もも','甘い','2020-11-30');
//update
UPDATE tbl set
title=COALESCE(ELT(FIELD(id,1,2,3),'ぶどう','いちご','ばなな'),title),
content=COALESCE(ELT(FIELD(id,1,2,3),'むらさき','あかい','黄色い'),content),
data=COALESCE(ELT(FIELD(id,1,2,3),'2020-10-11','2020-12-20','2020-10-30'),data)
//もしくは、テンポラリテーブルを作って流し込む
create temporary table tmp(id int primary key,title varchar(10),content varchar(10),data date);
insert into tmp values
(1,'ぶどう','むらさき','2020-10-11'),
(2,'いちご','あかい','2020-12-20'),
(3,'ばなな','黄色い','2020-10-30');
update tbl as t1,tmp as t2 set
t1.title=t2.title,
t1.content=t2.content,
t1.data=t2.data
where t1.id=t2.id
//全項目更新しているなら上記tmpをつかってreplace構文でもよいかも
replace tbl select * from tmp;
データが少量ですと、上記の記述が便利だな〜とは思うのですが、避けては通れぬ道ですね…！
idごとに横並びにしたデータは持っていたので、なんとかそのまま更新処理できないかなと思っていたのですが、難しそうでした…
丁寧なご回答ありがとうございます。
テンポラリテーブル…初めて聞きました。
調べつつ、もう一度方法を改めてみたいと思います。
上記コード参考にさせていただきます！！
mysqlのreplace構文は特殊で、実際には該当するデータを削除して、
新たにインサートしています
なるほど…replace構文、言葉は聞いたことあったのですが詳しくは知らなかったので、これを機に調べてみます！
0
UPDATE文はWHERE句にヒットしたレコードを更新します。
例として提示されたケースだと、
SQL
1WHERE id=1
のようにWHERE句でキーを指定したUPDATE文を20000行書くしかないと思います。
私だったらExcelのワークシートに更新後の値を設定して、マクロで更新処理を書きます。
投稿2020/09/16 03:28
退会済みユーザー
総合スコア0
0
case等を使うと、カラム指定と値指定を1回1回行わなければいけないので、
insert文のように、最初に変更したいカラム名を指定して、そのあとで値だけを指定したい
更新のバリエーションがあるんですから、そのバリエーション分の分岐は必要です。
sqlのみで行うならcase等での分岐ですけど、SQL文にその分岐を持ち込みたくないなら、データで行う必要があります。
更新先と更新値のテーブルを結合してupdateすれば、SQL文に分岐は必要なくなります。
当然ですが、更新値のデータはバリエーション分作成する必要があります。
質問のケースが、更新データが大量にあってそれを元に更新したいという事なら、そのデータを作業用のテーブルにLOAD DATA INFILE等でインポートして、そのテーブルを元に更新するのが手間が掛からないと思います。
投稿2020/09/16 03:38
編集2020/09/16 03:46
総合スコア25327
詳しいご回答ありがとうございます。
作業用テーブルを作成する方法もあるのですね…
やはり一括で行うというのは難しそうですね…
ありがとうございます！
以下が参考になれば
MySQLのLOAD DATA INFILEを使って登録または更新する方法
https://qiita.com/morimorimo__/items/3dbde55f80632efe446a
難しくて読み飛ばしていた記事でした…笑
ありがとうございます！！じっくり読んでみます！！
あなたの回答
tips
プレビュー
