パソコン情報

【Access】2つのテーブルから一致(重複)したデータだけ削除するSQLコードの謎

一致したデータだけ削除

やりたい事は、

やりたいポイント

大量のデータが登録されているテーブルから、消したいデータが登録されているテーブルとの同じフィールド「外注手配番号」というフィールドが一致したデータだけ消したいというものです。

 

 

今まで、コメントアウトしていたコードで動いていましたが、消したいデータテーブルのデータが存在しないとエラーが発生するようになってしまいました。

そこで、別のコードでエラー回避できましたが、データ検証の為にサンプルを作成してテストしてみました。

 

 

 

サンプルを準備して実験

テーブル(T1)が消したい方のデータになります。テーブル(T2)が消すリストになるデータになります。

適当なデータを入れて、フォームにボタンを設置してコードを準備しました。

 

 

ボタンをクリックしたら、T2テーブルのデータを元にT1テーブルの重複データが削除されます。

期待する結果は、下記のようになるはずです。

 

 

 

重複データを削除する2つのコード

Private Sub コマンド0_Click()

            Dim cn As New ADODB.Connection
            Dim cmd As New ADODB.Command
            Dim SQL As String
            
            Set cn = CurrentProject.Connection
            Set cmd = New ADODB.Command
            cmd.ActiveConnection = cn

            SQL = "DELETE DISTINCTROW T1.* "
            SQL = SQL & "FROM T1 INNER JOIN T2 ON T1.NO = T2.NO"
            cmd.CommandText = SQL
            cmd.Execute



'            SQL = "DELETE T1.* "
'            SQL = SQL & "FROM T1 "
'            SQL = SQL & "Where Exists( Select 1 From T2 Where T2.NO = T1.NO ) = True"
'            cmd.CommandText = SQL
'            cmd.Execute


            Set cmd = Nothing
            cn.Close: Set cn = Nothing
End Sub

 

上のコードには、重複データを消すために、コメントアウトしてない方のコードと、コメントアウトした方のコードの2種類があります。

結果は、

さらに詳しく

どっちのコードでも求める結果が出た!

 

しかし、大量のデータを実稼働させている方では、

コメントアウトしているコードじゃないとエラーがでて処理できませんでした。

 

 

DISTINCTROW付加してJOINを使用!?

 

DELETEするのに、JOINを使って処理するとエラーがでたので、探してDISTINCTROWを付加すると使える事がわかりました。

ココに注意

DISTINCTROWの意味が理解しにくい!

 

これを付けるとどうなるのか!?ということを理解することが本当に難解でした。

なんと説明したらいいのかわかりませんので、サンプルに基づいて解説してみたいと思います。

 

比較するテーブル(T2)に、同じレコードが重複しているとエラーが出ることがわかりました。

T2テーブルに同じレコードがあり重複している場合に、DISTINCTROWを付けると、比較フィールドをまとめてエラーを出さなくするようです。

 

 

どっちのコードを使ったらいいのか!?

もうひとつのコードの方は、DISTINCTROWを付けなくてもエラーも出ずに処理ができました。

そうると、仮に比較するテーブルにデータが重複してもエラーにならないので下記のコードの方がいいのかもしれませんね。

 

SQL = "DELETE T1.* "
SQL = SQL & "FROM T1 "
SQL = SQL & "Where Exists( Select 1 From T2 Where T2.NO = T1.NO ) = True"

 

Where Existsは、副問い合せの結果が存在するかを調べるときに使用するらしい。

意味が不明

 

自分の推測ですが、(Select 1 From T2 Where T2.NO = T1.NO )で、T1とT2でNOが同じT2のレコードが存在するこをと問い合わせしており、一致するデータが存在するときだけ、結果を返すという感じかと思います。

ちなみに、T1テーブルに重複するレコードは、T2で比較して全部削除されます。

 

とにかく、求めた答えが出ればどっちでもいいのですが、こっちの方が良さそうですね。