Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-22405

Unable to delete data using CTE

    XMLWordPrintable

Details

    Description

      create table CONTACT_EMAIL (
      ID INT auto_increment primary key,
      CONTACT_NAME VARCHAR(50) default null,
      CONTACT_EMAIL VARCHAR(50) not null
      );

      insert into contact_email(Contact_email,contact_name)
      VALUES
      ('info@kimann.com','SK NG'),
      ('info@kimann.com','SL LIM'),
      ('strip2@kimann.com','SK NG'),
      ('strip@kimann.com','SL LIM');

      ;with recursive dupemail as (select id ,contact_email,row_number() OVER(partition by contact_email order by id,contact_email) as ROWID from contact_email)
      select * from dupemail;

      /UNABLE to delete the records using CTE/
      ;with recursive dupemail as (select id ,contact_email,row_number() OVER(partition by contact_email order by id,contact_email) as ROWID from contact_email)
      delete from contact_email ce where id in (select id from dupemail where rowid>1);

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              DennisK Dennis Kwone
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.