Affects Version/s: None
Fix Version/s: None
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 This MERGE statement is not supported. Reason: Non-unique ON clause not allowed with INSERT.
- ERROR 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,
primary key (movie_id));
create unique index movie_title on movie (movie_title);
merge into movie using (select * from movie_staging) m (
) 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 (