Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-2715

MERGE only works with PRIMARY KEY and not UNIQUE INDEX for ON column(s)

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: sql-general
    • Labels:
      None

      Description

      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);

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              RohitJain13 Rohit Jain
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: