Search code examples
sqloracle11g

Missing NULL keyword error when trying to avoid duplicates

I'm trying to do an update to a table that contains records pertaining to people under Oracle 11g. I have a column that contains IDs, some of which contain hyphens. The table has a constraint on this ID so two different people can't have the same ID. My goal is to remove the hyphens, while avoiding any constraint violations when I do an update. So I tried this query to detect records that would violate the constraint:

SELECT count(*) FROM people
WHERE externalid LIKE '%-%' AND
replace(externalid, '-') IS IN (SELECT externalid FROM people);

This then fails with an ORA-00908: missing NULL keyword. I know this is due to that last line, but I don't know how else to structure that part of the query to detect the records that have the non-hyphenated ID already in use. How can I detect these records that would violate the constraint?

Solution

  • The IS keyword is used as in something IS NULL, not with the IN keyword. That's why the database thinks that there is a NULL missing. Just remove the IS:

    SELECT count(*) FROM people
WHERE externalid LIKE '%-%' AND
replace(externalid, '-') IN (SELECT externalid FROM people);