[SOLVED] Delete logic for duplicate data is throwing ORA-00913: too many values

Delete logic for duplicate data is throwing ORA-00913: too many values

I am trying to perform delete on a table containing Duplicate values using the SQL logic below but oracle is throwing ORA-00913: too many values error when executed.

DELETE
FROM
  UT_USER
WHERE
  ROWID NOT IN
  (
    SELECT
      MAX(ROWID),
      USER_NAME,
      MODULE,
      EFF_BEGIN_DT,
      EFF_END_DT,
      STATUS,
      EDW_CREATE_DATE_TIME,
      EDW_UPDATE_DATE_TIME,
      EDW_CREATED_BY,
      EDW_UPDATED_BY,
      EDW_UPDATE_NOTE
    FROM
      UT_USER
    GROUP BY
      USER_NAME,
      MODULE,
      EFF_BEGIN_DT,
      EFF_END_DT,
      STATUS,
      EDW_CREATE_DATE_TIME,
      EDW_UPDATE_DATE_TIME,
      EDW_CREATED_BY,
      EDW_UPDATED_BY,
      EDW_UPDATE_NOTE
  ) ;

Solution

The SELECT statement in your NOT IN returns a number of columns. It has to return exactly one column to be valid (hence the error). Fortunately, it looks like you can just eliminate the extra columns you are selecting

DELETE
FROM
  UT_USER
WHERE
  ROWID NOT IN
  (
    SELECT
      MAX(ROWID)
    FROM
      UT_USER
    GROUP BY
      USER_NAME,
      MODULE,
      EFF_BEGIN_DT,
      EFF_END_DT,
      STATUS,
      EDW_CREATE_DATE_TIME,
      EDW_UPDATE_DATE_TIME,
      EDW_CREATED_BY,
      EDW_UPDATED_BY,
      EDW_UPDATE_NOTE
  ) ;