okinawa

IT勉強メモ

NOT EXISTSとDISTINCTでハマった話

大して難しいことではなかったのにハマったので備忘録。

やりたかったこと

投入データでテーブルを更新

投入データに含まれていないけど同じ日付のデータも更新する

下記の例で行くと2023/04/16 group01 02が投入データ。
投入データで更新した後に同日付、2023/04/16 group03と04にだけupdatecheckを入れたい。

更新対象 table2

投入データ table3

・更新SQL

update table2
set
table2.value = t3.value
from table2 t2
left join table3 t3
on t2.tenpo = t3.tenpo
and t2.date = t3.date
and t2.[group] = t3.[group]
where
t3.tenpo IS NOT NULL

・更新後

テーブル結合の結果をイメージできてなかった

最初に書いたSQL

select * from table2 t2
left outer join table3 t3
on t2.tenpo = t3.tenpo
and t2.date = t3.date
and t2.[group] = t3.[group] 
where
t2.tenpo = t3.tenpo
and t2.date = t3.date
and t2.[group] <> t3.[group] 

where句で and t2.[group] <> t3.[group]  しているけどt2とt3で異なるgroupは結合されてないので存在しない。

下記の2023/04/16のグループ03と04を抽出したかった↓

whereなしで結合した結果

DISTINCTを勘違いしていた

そこで次に考えたSQLが下記。

結合条件からグループを消してWhere句で絞り込んでなんとかしたったのだけど行が増殖して困った。

DISTINCT table2.* にするだけでOK。

しかしDISTINCTは1列にしか効かないと思い込んでいたため増殖する時点でアカンと思ってしまった。

select * from table2 t2
left outer join table3 t3
on t2.tenpo = t3.tenpo
and t2.date = t3.date
--where
--なにかの条件

EXISTSを使い慣れていなかった

「ditinct 複数列」とかで検索して行増殖はなんとかなったけど最後に立ちはだかったのはEXISTS。

そもそもEXISTSを使い慣れていなかったので思い浮かばなかった。

まず書いたのはこれ。

結合条件にグループを入れてないから異なるグループとも結合されている。

なのでEXISTSで t2.[group] <> t3.[group]  しても意味無し。

select distinct t2.* from table2 t2
left outer join table3 t3
on t2.tenpo = t3.tenpo
and t2.date = t3.date
where
t3.tenpo IS NOT NULL
and
EXISTS (
select * from table3
where
t2.tenpo = t3.tenpo
and t2.date = t3.date
and t2.[group] <> t3.[group] 
)

正解

下記をUPDATE SELECT文に組み込めばOKだった。

select distinct t2.* from table2 t2
left outer join table3 t3
on t2.tenpo = t3.tenpo
and t2.date = t3.date
where
t3.tenpo IS NOT NULL
and
NOT EXISTS (
select * from table3 t4
where
t2.tenpo = t4.tenpo
and t2.date = t4.date
and t2.[group] = t4.[group] 
)

感想

納期が迫っていて焦ってしまい、だいぶ遠回りをしてしまった。

あとはお勉強だけじゃなくて実践も大切だなあという感想。

もっとバリバリSQLもコードも書きたい。