Duplicate MERGE key detected in join

Massimo_Loporch

 

I'm using HP-Vertica 7.1.1 comunity edition and 

I can not understand why the merge below goes wrong:

 

create table t1 (id_pdv varchar(6),id_articolo varchar(6),id_data date, fl_tipoagiesse char(1)) ;
insert into t1(id_pdv , id_articolo,id_data,fl_tipoagiesse)
values( '018500', '681101', '2014-10-09', ' ' );
insert into t1(id_pdv , id_articolo,id_data,fl_tipoagiesse)
values('018500', '681101', '2014-10-10', ' ' );
insert into t1(id_pdv , id_articolo,id_data,fl_tipoagiesse)
values('018500', '681101', '2014-10-11', ' ' );
insert into t1(id_pdv , id_articolo,id_data,fl_tipoagiesse)
values('018500', '681101', '2014-10-15', ' ' );

 

select * from t1;
/*
id_pdv id_articolo id_data fl_tipoagiesse
018500 681101 09/10/2014
018500 681101 10/10/2014
018500 681101 11/10/2014
018500 681101 15/10/2014
*/

 

create table t2 (id_pdv varchar(6),id_articolo varchar(6),id_datainizio date, id_datafine date, fl_tipoagiesse char(1)) ;
insert into t2(id_pdv , id_articolo,id_datainizio,id_datafine, fl_tipoagiesse)
values( '018500', '681101', '2014-10-08', '2014-10-21', 'N' );
insert into t2(id_pdv , id_articolo,id_datainizio,id_datafine, fl_tipoagiesse)
values( '018500', '681101', '2014-09-24', '2014-10-07', 'N' );

select * from t2;
/*
id_pdv id_articolo id_datainizio id_datafine fl_tipoagiesse
018500 681101 24/09/2014 07/10/2014 N
018500 681101 08/10/2014 21/10/2014 N
*/

 

merge into t1 using t2
on t1.ID_Pdv=t2.ID_Pdv and t1.ID_Articolo=t2.ID_Articolo
and t1.ID_data between ID_DataInizio and ID_DataFine
when matched then update
set FL_TipoAgiesse= t2.FL_TipoAgiesse ;

 

 

[3147] [Vertica][VJDBC](3147) ERROR: Duplicate MERGE key detected in join [(public.t1 x public.t2) using t1_b0 and subquery (PATH ID: 1)]; value [018500,681101]

 

thanks in advance.

 

ML