update文

とある１レコードの値で本番のデータを全件更新してしまうというヤラカシをやってしまったので、次回は気をつける意を込めた記録です。 SQLはcompile sql server onlineを使用します。

仕様

• tbl1.val1をtbl2.val2へと更新する
• 更新の条件
  • tbl1.idとtbl2.idを結合し、tbl2.val2 = 'foo'の時だけ更新する
  • tbl2.val2 <> 'foo'の時は更新しない

tbl1

tbl2

ということでこの場合、id = {10,30,80}が更新対象となります。

問題のコード(ヤラカシあり)

-- データ作成 ここから
create table tbl1  (
 id int primary key
  , val1 nvarchar(10) null
)
;

insert into tbl1 values
  (10,'a')
  ,(20,'b')
  ,(30,'c')
  ,(40,'d')
  ,(50,'e')
  ,(60,'f')
  ,(70,'g')
  ,(80,'h')
  ,(90,'i')
;

create table tbl2 (
 id int primary key
  , val2 nvarchar(10) null
  , constraint fk_id foreign key(id) references  tbl1
)
;

insert into tbl2 values
  (10,'foo')
  ,(30,'foo')
  ,(50,'baz')
  ,(60,'baz')
  ,(80,'foo')
;
-- データ作成 ここまで



--  一時テーブルに tbl1 のレコードを格納
select * into #t1 from tbl1
;


-- tbl1 更新前
select * from tbl1
;

-- 更新処理
update tbl1
set val1 = tbl2.val2
from #t1 
  inner join tbl2 
    on #t1.id = tbl2.id 
    and tbl2.val2 = 'foo'
;


-- tbl1 更新後
select * from tbl1
;

実行結果

上：更新前のtbl1, 下：更新後のtbl1なのですが、見ての通り残念な感じに更新されてしまいました。。

better なコード

★の箇所のように、updateで更新対象となるテーブルをinner join してあげるとinner join で抽出される結果通りに更新されます。

update tbl1
set val1 = tbl2.val2
from #t1 
  inner join tbl2 
    on #t1.id = tbl2.id 
    and tbl2.val2 = 'foo'
  inner join tbl1 on tbl1.id = #t1.id -- ★
;

better なコードの実行結果

仕様を満たした更新結果となりました。

まなび

更新対象のテーブルをちゃんとjoin しましょう。