ORA-01722: invalid number in Update query-CodePudding

I have following query and getting ORA-01722: invalid number

UPDATE SCHEDULE SET IS_ACTIVE = 0 WHERE REMINDER_ID IN ( SELECT replace('1,2', '''', '') as xtx FROM DUAL);

When I tried following way it executes , SELECT replace('1,2', '''', '') FROM DUAL; I am getting (1,2) but this is not happening in the first query.

CodePudding user response：

Got it and working - x_reminder_id = "'12,13'"

   MERGE INTO SCHEDULE dst
  USING (
    SELECT REGEXP_SUBSTR(x_reminder_id,        '\d ', 1, LEVEL) AS id
    FROM   DUAL
    CONNECT BY LEVEL <= LEAST(
                          REGEXP_COUNT(x_reminder_id,        '\d ')
                        )
  ) src
  ON (src.id = dst.REMINDER_ID)
  WHEN MATCHED THEN
    UPDATE
      SET IS_ACTIVE = 0;