[jira] [Created] (TRAFODION-2715) MERGE only works with PRIMARY KEY and not UNIQUE INDEX for ON column(s)

["Rohit Jain (JIRA)" <ji...@apache.org>]

Rohit Jain created TRAFODION-2715:
-------------------------------------

             Summary: MERGE only works with PRIMARY KEY and not UNIQUE INDEX for ON column(s) 
                 Key: TRAFODION-2715
                 URL: https://issues.apache.org/jira/browse/TRAFODION-2715
             Project: Apache Trafodion
          Issue Type: Improvement
          Components: sql-general
            Reporter: Rohit Jain
            Priority: Minor


I have a movie table where I need the system to generate a unique primary key for it, or syskey, movie_id.  However, I want to enforce the uniqueness constraint on another field of the table, movie_title, so that I don't have duplicate entries in the table for the same movie.  So I created a unique alternate index on it.  See the DDL below. 

However, when I tried to do a MERGE into this table, to essentially eliminate the duplicate rows that were in the source table (see statement below), I got an error:
*** ERROR[3241] This MERGE statement is not supported. Reason:  Non-unique ON clause not allowed with INSERT.

*** ERROR[8822] The statement was not prepared.

Essentially, I was forced to load the table into a temporary table with movie_title as its primary key, where the MERGE worked, and then load it from that table to the movie table I needed it in, in the first place.  Now, with a small amount of data this was fine.  But for a very large dataset, this would not be an added desirable step.  

A unique alternate index should work just as well as a PK does, in this case.

create table movie
(movie_id smallint unsigned generated by default as identity,
movie_title char(100),
title_year smallint,
content_rating char(10),
imdb_score numeric(3,1),
num_critic_for_reviews smallint,
num_user_for_reviews smallint,
num_voted_users int,
movie_facebook_likes int,
cast_total_facebook_likes int,
duration smallint,
budget largeint,
gross int,
color char(16),
aspect_ratio numeric(4,2),
country char(20),
language char(10),
facenumber_in_poster smallint,
genres char(100),
plot_keywords char(150),
movie_imdb_link char(100),
primary key (movie_id));

create unique index movie_title on movie (movie_title);

merge into movie using (select * from movie_staging) m (
movie_id,
movie_title,
title_year,
content_rating,
imdb_score,
num_critic_for_reviews,
num_user_for_reviews,
num_voted_users,
movie_facebook_likes,
cast_total_facebook_likes,
duration,
budget,
gross,
color,
aspect_ratio,
country,
language,
facenumber_in_poster,
genres,
plot_keywords,
movie_imdb_link
) on movie_title = m.movie_title 
WHEN MATCHED THEN UPDATE SET 
title_year = m.title_year,
content_rating = m.content_rating,
imdb_score = m.imdb_score,
num_critic_for_reviews = m.num_critic_for_reviews,
num_user_for_reviews = m.num_user_for_reviews,
num_voted_users = m.num_voted_users,
movie_facebook_likes = m.movie_facebook_likes,
cast_total_facebook_likes = m.cast_total_facebook_likes,
duration = m.duration,
budget = m.budget,
gross = m.gross,
color = m.color,
aspect_ratio = m.aspect_ratio,
country = m.country,
language = m.language,
facenumber_in_poster = m.facenumber_in_poster,
genres = m.genres,
plot_keywords = m.plot_keywords,
movie_imdb_link = m.movie_imdb_link
WHEN NOT MATCHED THEN INSERT VALUES (
movie_id,
movie_title,
title_year,
content_rating,
imdb_score,
num_critic_for_reviews,
num_user_for_reviews,
num_voted_users,
movie_facebook_likes,
cast_total_facebook_likes,
duration,
budget,
gross,
color,
aspect_ratio,
country,
language,
facenumber_in_poster,
genres,
plot_keywords,
movie_imdb_link);




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)