We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Duplicate MERGE key detected in join — Vertica Forum

Duplicate MERGE key detected in join

 

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

 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file