事故を起こさないupdate文の書き方 by sql serverのクエリ

この記事を読むのに必要な時間は約 19 分です。

update文

危険度

SQL文で、Selectなり、insert、delete、update等があると思います。

この中で一番事故が起こりやすいのがupdateです。

まず、select文は最も事故が起きにくいです。理由は見るだけですから悪影響を与える

事が普通は無いからです。

ただ、100万×100万且つソートなんかするとテンポラリテーブルが大爆発する事もありますので、意識だけはしておきましょう。

次に、insert文です。

insert文ですが、これは事故が起こってしまっても、何とかリカバリできる類の物です。

間違って挿入してしまったら、挿入してしまった物を削除すれば良いだけですから。

 

ただし、厄介なのがupdateです。

updateは間違って更新してしまった場合、どこがどのように更新されてしまったのかわからない事が多いからです。

復旧させるのも大変なので、事故が起こらないように注意しましょう。

 

事故を起こさないようにするためのupdate文の書き方

テストテーブル作成

今回、中級編として説明しますが、やってる事は初級の内容です。

ただ、考え方は中級編レベルでも良いかなと思います。

以下、サンプルテーブルで説明しましょう。

以前、作った「おさかな」テーブルがありますのでこのテーブルを利用して説明します。

 

 

このお魚テーブルを残しておきたいので、このお魚テーブルの複製テーブルを

おさかな３

で作りたいと思います。

作り方は此方の記事で詳細を説明していますので見てみてください。

SQL 検索結果を使って一気にテーブル作成

このページではSQL文を書く上で上級者向けのテクニックです。 と言っても慣れてしまえばたいしたことはありません。大事な事は そんなやり方があるんだ！！ という、その事実に気づくところです。 ...

you-1.tokyo

↑を見てくれているという想定でサラッと行きます。

select * into [おさかな3] 
from
(
SELECT   [種別]
      ,[月]
      ,[名前]
      ,[単価]
      ,[数量]
  FROM [sampleDB].[dbo].[おさかな]
  )XXX

 

↓

CTRL＋Shift+R

はい。ちゃんと赤線も消せましたね。

 

お題は、9月の魚の数量は一律100にする

[speech_bubble type=”drop” subtype=”L1″ icon=”02ukiyoe/yukiji_R.png” name=”ゆきじさん”]9月の魚を全部100匹にしておいてくれ[/speech_bubble]

まぁ適当なお題です。

お題そのものにはあまり意味はありません。

では実際に書いてみましょう。

update [おさかな3] set
   数量=100
  FROM [sampleDB].[dbo].[おさかな3]
  where 月=' 9月'

↓

こんな感じです。

まったくたいしたことないです。

結果を確認してみたいと思います。

• 月が9月
• 数量が100

とかにしておけば検索は簡単ですね。

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT TOP (1000) [種別]
      ,[月]
      ,[名前]
      ,[単価]
      ,[数量]
  FROM [sampleDB].[dbo].[おさかな3]
  where 月=' 9月' and [数量]='100'

 

ちゃんと、更新も成功していますね。

 

依頼者に結果を確認してみると・・・

依頼者に結果を確認してみると大変不愉快な事を言われることがあります。

[speech_bubble type=”drop” subtype=”L1″ icon=”02ukiyoe/taro_R.png” name=”たろさん”]終わりましたー確認してください！[/speech_bubble]

[speech_bubble type=”drop” subtype=”R1″ icon=”02ukiyoe/yukiji_L.png” name=”ゆきじさん”]蛸は魚じゃないだろ！！！[/speech_bubble]

[speech_bubble type=”drop” subtype=”L1″ icon=”02ukiyoe/taro_R.png” name=”たろさん”]いや、まぁそうですけど普通やりますよね？[/speech_bubble]

[speech_bubble type=”drop” subtype=”R1″ icon=”02ukiyoe/yukiji_L.png” name=”ゆきじさん”]お前の普通を押し付けるな！！[/speech_bubble]

なんとも不毛なやり取りですね。

依頼された方からすれば、ここは更新してしまうのが当たり前だと思います。

ただ、依頼した方からすれば、想定外な動作だったりすることもあるわけですね。

それでは、この様な不毛なやり取りをしないためにも何をしたらよかったと思いますか？

 

依頼者に確認をお願いしたのはなぜか？

なんで、依頼者に確認をお願いしたか？

ここで、疑問が出る人は駄目です！

何かをお願いされて、それを行ったのであれば、依頼元に結果の確認と報告をお願いする事は当たり前です。

蔑ろにしてはいけませんよ。

ただ、結果の確認をしてNGをくらってやり直しができないのであれば、その確認方式は何かが足りなかったかもしれませんね。

やり直しができないのであれば、結局は意味が無いですからね。

今回はupdate文を行う作業でしたので、実行しなければ確認をお願いできないと思ったかもしれませんが、一工夫する事により、実行前の確認もできましたよ。

 

一工夫した書き方

環境の復元

「おさかな３」は失敗してしまったので、「おさかな４」テーブルを作りましょう。

SQL文はこれです。

select * into [おさかな4] 
from
(
SELECT   [種別]
      ,[月]
      ,[名前]
      ,[単価]
      ,[数量]
  FROM [sampleDB].[dbo].[おさかな]
  )XXX

 

上の方で書いたSQL文のテーブル名が「おさかな３」から「おさかな４」に変わっただけです。

 

先にSelectするだけ

ここからが本題です。

updateする前に一度Select文を書いてみます。

ここで書くSelect文は

依頼者からの依頼に従った出来上がりをイメージしたSelect文です。

[speech_bubble type=”drop” subtype=”L1″ icon=”02ukiyoe/yukiji_R.png” name=”ゆきじさん”]9月の魚を全部100匹にしておいてくれ[/speech_bubble]

依頼内容は↑です

SELECT TOP (1000) [種別]
,[月]
,[名前]
,[単価]
,[数量]
FROM [sampleDB].[dbo].[おさかな4]
where 月=' 9月'

 

依頼事項の抽出条件は 9月　だけです。

なので、where句も条件は 9月　だけにします。

次に、update文を意識して要らない情報を削ります。

SELECT  [月]
      ,[名前] 
      ,[数量]
  FROM [sampleDB].[dbo].[おさかな4]
  where 月=' 9月'

ここまで削れば十分ですね。

次に、変更後の値を意識して単純にその値を表示するような一列を追加します。

 

たったのこれだけです。

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT  [月]
      ,[名前] 
      ,[数量]
　　　　,'100'
  FROM [sampleDB].[dbo].[おさかな4]
  where 月=' 9月'

ここまで出来たら依頼者に対してupdateを行う前に、確認すれば良いのです。

[speech_bubble type=”drop” subtype=”L1″ icon=”02ukiyoe/taro_R.png” name=”たろさん”]これでいいですか？[/speech_bubble]

[speech_bubble type=”drop” subtype=”R1″ icon=”02ukiyoe/yukiji_L.png” name=”ゆきじさん”]いやいや、蛸は魚じゃないだろ！！[/speech_bubble]

[speech_bubble type=”drop” subtype=”L1″ icon=”02ukiyoe/taro_R.png” name=”たろさん”]それじゃ蛸は外しておきますねー[/speech_bubble]

[speech_bubble type=”drop” subtype=”R1″ icon=”02ukiyoe/yukiji_L.png” name=”ゆきじさん”]いや、ごめんやっぱり蛸も100に更新しておいて。よろしくね[/speech_bubble]

どうでしょうか？

もめないですよね？

依頼された方も、依頼主に対して先に確認ができたので無駄なやり直しが発生しません。

 

それでは　ここからが一番大事なポイントなので、細かいステップで説明していきます。

※サンプルがすごく少ないので、蛸も更新する前提でいきます。もし、蛸を外しておきたかったのであれば、where句に追加で名前<>’蛸’とかしておけばよいです。

Step1初期値

この状態からスタートします。

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT  [月]
      ,[名前] 
      ,[数量]
      ,'100'
  FROM [sampleDB].[dbo].[おさかな4]
  where 月=' 9月'

 

Step2赤枠の部分を一切触らない。

今からupdateをかけて行きますが、以下の赤枠の部分は今後一切さわらないでください。

折角依頼元に対して事前確認までした部分です。この場所が変わらなければupdateされる対象もかわりませんのでfrom句よりも下側は触らない事を意識しておいてください。

Step3いらない場所を削る

今ら行うのはupdate文です。表示されるだけの項目は要りませんので削ります。

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 

       [数量]
      ,'100'
  FROM [sampleDB].[dbo].[おさかな4]
  where 月=' 9月'

この状態だと、何か変更になるか表示されないのでちょっと不安ですよね。

それでも、Step2の内容を守ってさえいれば、対象は頭にはいっているので

問題無いですよね？

Step4 select を update SET に書き換える

実行しちゃうとエラーになるので画面だけです。

↓

 

Step5 [数量]と100を=(イコール)で結ぶ

以下の赤赤枠部分を書き換えます。

↓

Step6 テーブル名を含める

最後に、テーブル名を含めます。

はい。

この様な形です。

実行してみます。

この様にいきなりupdate文を実行するのではなく、一度、selectして、そのselectをupdateに書き換えてください。

そうする事によって

変更対象の値に自信をもって操作することが可能となります。

 

事故が起きてしまったら大変です。

ぶっちゃけ、やり無とかができない事の方が圧倒的に多いでしょう。

更新系の操作をするときは、慎重になってやりましょう。

 

サンプルテーブル

お魚テーブルが無い人は以下、コピペで作ってください。

サンプル