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

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

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration