差分追加更新のためのSQL

• MERGE ステートメント
• delete insert
  • http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_updating-inserting-using-staging-tables-.html
  • https://www.postgresql.jp/document/9.1/html/sql-delete.html
• FULL OUTER JOIN

delete insert

• redshiftの例引っ張ってきた
• join version(昔ハマった)
• ハマったときに諦めてやったin

delete_using.sql

delete from target 
using stage 
where target.primarykey = stage.primarykey;

delete_table_as.sql

delete a
from target as a
inner join stage
on target.primarykey = stage.primarykey;

delete_in.sql

delete
from target
where primarykey in (
  select primarykey from stage
)

delete_after.sql

insert into target 
select * from stage;

MERGE

merge into target
using stage
on target.primarykey = t2.primarykey
when matched then
  update set target.column_1 = stage.column_1
when not matched then
  insert values (stage.primarykey, stage.column_1)
;

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

update target a 
set column_1 = column_1
from stage
where a.id = stage.id;

FULL OUTER JOIN

旧verも更新されつつ新verも更新されるパターンで使った

• めんどう
• 自由度が高い

full_outer.sql

select
  case
    when stage.primarykey is not null then stage.primarykey 
    else target.primarykey
  end as primarykey
  , case
    when stage.primarykey is not null then stage.column_1 
    else target.column_1
  end as column_1
  -- etc.
from target
full outer join stage
on target.primarykey = stage.primarykey