关于kadb数据库如何改写merge into语法的说明

首先Kadb是不支持的merge into语法的，但是kadb 在使用加载工具gpload的时候是支持merge这种方式的，单这种方式只支持文本文件加载使用上有一定的局限性。

同时可以根据merge into的功能来通过sql的方式也可以替换merge into，替换方式主要分3步，更新、删除、插入。

以下面sql为例

merge into fact_port_front_vess_crane fact

using(

select

scd_id,

crane_type,

cwp_machno,

subport,

subport_id,

creation_time,

last_update_time,

current_ts,

extend_field_del_flag

from(

select

t1.scd_id,

dim_gantry.gcn_type crane_type,

t1.cwp_machno,

t1.subport,

t1.subport_id,

t1.creation_time,

t1.last_update_time,

t1.current_ts,

t1.extend_field_del_flag

from(

select

distinct

a.scd_id,

coalesce(d.cwp_machno,c.cwp_machno) cwp_machno,

a.subport,

dim_subport.subport_id subport_id,

now() creation_time,

now() last_update_time,

b.current_ts,

b.extend_field_del_flag

from

(select

distinct

scd_id,

subport,

vpc_vcw_stsid,

vpc_vcw_id

from dwi_port_cntr_stowage_plan_containers 

) a

join

dwi_port_mach_crane_works b 

on coalesce(a.vpc_vcw_stsid,a.vpc_vcw_id) = b.vcw_id 

and a.subport = b.subport  

join

dwi_port_mach_crane_work_plans c 

on c.cwp_id = b.vcw_cwp_id 

and c.subport = b.subport

left join

dwi_port_crane_virtual_compare d

on c.cwp_machno =d.virtual_code

and c.subport = d.subport

left join

dim_port_subport dim_subport 

on a.subport = dim_subport.subport

)t1

left join

dim_port_mach_gantry_cranes dim_gantry

on t1.cwp_machno =dim_gantry.gcn_gcraneno

)a

where crane_type is not null

)dwi

on fact.scd_id = dwi.scd_id and fact.subport = dwi.subport



when matched then

update

    set

fact.crane_type  = dwi.crane_type,

fact.cwp_machno  = dwi.cwp_machno,

fact.creation_time  = dwi.creation_time,

fact.last_update_time  = dwi.last_update_time,

fact.current_ts  = dwi.current_ts,

fact.extend_field_del_flag  = dwi.extend_field_del_flag



when not matched then

insert

    (id,

    scd_id,

    crane_type,

    cwp_machno,

    subport,

    subport_id,

    creation_time,

    last_update_time,

    current_ts,

    extend_field_del_flag   

    )

values

    (NEXTVAL('seq_fact_port_front_vess_crane'),

    dwi.scd_id,

    dwi.crane_type,

    dwi.cwp_machno,

    dwi.subport,

    dwi.subport_id,

    dwi.creation_time,

    dwi.last_update_time,

    dwi.current_ts,

    dwi.extend_field_del_flag

);

1. 首先确定数据集

先将dwi作为一个临时表提取出来作为数据来源表，这一步对merge into 没有影响

create temp table dwi as

select

scd_id,

crane_type,

cwp_machno,

subport,

subport_id,

creation_time,

last_update_time,

current_ts,

extend_field_del_flag

from(

select

t1.scd_id,

dim_gantry.gcn_type crane_type,

t1.cwp_machno,

t1.subport,

t1.subport_id,

t1.creation_time,

t1.last_update_time,

t1.current_ts,

t1.extend_field_del_flag

from(

select

distinct

a.scd_id,

coalesce(d.cwp_machno,c.cwp_machno) cwp_machno,

a.subport,

dim_subport.subport_id subport_id,

now() creation_time,

now() last_update_time,

b.current_ts,

b.extend_field_del_flag

from

(select

distinct

scd_id,

subport,

vpc_vcw_stsid,

vpc_vcw_id

from dwi_port_cntr_stowage_plan_containers 

) a

join

dwi_port_mach_crane_works b 

on coalesce(a.vpc_vcw_stsid,a.vpc_vcw_id) = b.vcw_id 

and a.subport = b.subport 

join

dwi_port_mach_crane_work_plans c 

on c.cwp_id = b.vcw_cwp_id 

and c.subport = b.subport

left join

dwi_port_crane_virtual_compare d

on c.cwp_machno =d.virtual_code

and c.subport = d.subport

left join

dim_port_subport dim_subport 

on a.subport = dim_subport.subport

)t1

left join

dim_port_mach_gantry_cranes dim_gantry

on t1.cwp_machno =dim_gantry.gcn_gcraneno

)a

where crane_type is not null;

1. 执行更新语句

先执行更新语句，目标表和来源表两个表关联更新，对目标表已有的数据进行更新，即更新目标表中已有的数据。

update fact_port_front_vess_crane  fact

 set

  crane_type  = dwi.crane_type,

cwp_machno  = dwi.cwp_machno,

creation_time  = dwi.creation_time,

last_update_time  = dwi.last_update_time,

current_ts  = dwi.current_ts,

extend_field_del_flag  = dwi.extend_field_del_flag

 from

 dwi

 where fact.scd_id = dwi.scd_id::varchar  and fact.subport = dwi.subport::varchar;

1. 执行删除语句

删除来源表中已经更新的数据。

DELETE FROM dwi  USING fact_port_front_vess_crane  fact  WHERE  fact.scd_id = dwi.scd_id::varchar  and fact.subport = dwi.subport::varchar;

1. 执行插入语句

向目标表中插入，来源表删除后的数据，即目标表不存在的数据。

INSERT INTO fact_port_front_vess_crane

(id,

    scd_id,

    crane_type,

    cwp_machno,

    subport,

    subport_id,

    creation_time,

    last_update_time,

    current_ts,

    extend_field_del_flag   

    )

(SELECT  

    NEXTVAL('seq_fact_port_front_vess_crane'),

    dwi.scd_id,

    dwi.crane_type,

    dwi.cwp_machno,

    dwi.subport,

    dwi.subport_id,

    dwi.creation_time,

    dwi.last_update_time,

    dwi.current_ts,

    dwi.extend_field_del_flag

FROM (SELECT *, row_number() OVER (PARTITION BY scd_id,subport) AS gpload_row_number FROM dwi) AS dwi WHERE gpload_row_number=1);