T-SQL CAST only failing when within IN clause-CodePudding

I have the following two tables (in SQL Server 2017):

Parts

PartCode INT
IsActive BIT
...other fields...

Products

PartCode VARCHAR(6)
...other fields...

In the Products table, PartCode is either a 6-digit number or alpha followed by 5 digits. The Parts table is only concerned with those parts with 6-digit numeric part codes.

This query successfully returns the list of numeric part codes from the Products table:

SELECT CAST(PartCode AS INT) FROM Products WHERE ISNUMERIC(PartCode)=1

However, once I embed it within an IN, like this:

UPDATE Parts
SET IsActive=0
WHERE PartCode NOT IN (SELECT CAST(PartCode AS INT)
                       FROM Products
                       WHERE ISNUMERIC(PartCode)=1)

it fails with "Conversion failed when converting the varchar value 'K12345' to data type int."

I am aware of the odd behaviors of ISNUMERIC (unexpected values that it returns 1 for), but in this case, SELECT ISNUMERIC('K12345') is 0 as expected.

Since ISNUMERIC properly excluded the K12345 value on the SELECT, why did it attempt to cast it with the UPDATE? It should have been excluded from the result set (as it was when running the SELECT by itself) and thus not need to be converted. Why does placing the SELECT within an IN make it behave differently?

CodePudding user response：

Other than the obvious "fix the database", you can avoid most problems by using EXISTS().

UPDATE Parts
   SET IsActive=0
 WHERE NOT EXISTS (
         SELECT *
           FROM Products
          WHERE Products.PartCode = CAST(Parts.PartCode AS VARCHAR(6))
       )

This way you're converting an integer to a string, and it always works. It also continues to allow use of any index on Products.PartCode.

CodePudding user response：

Put the check in the select term:

UPDATE Parts
SET IsActive=0
WHERE PartCode NOT IN (
    SELECT
        CASE
            WHEN ISNUMERIC(PartCode)=1 THEN CAST(PartCode AS INT)
            ELSE -1
        END
    FROM Products)

The reason it's exploding in your version is the optimizer must be applying the where clause after evaluating the select list.

CodePudding user response：

Instead of trying to use ISNUMERIC - you can use LIKE:

UPDATE Parts
SET IsActive=0
WHERE PartCode NOT IN (SELECT CAST(pd.PartCode AS INT)
                       FROM Products pd
                       WHERE pd.PartCode LIKE '[0-9]%')

This will check the first character and include only those that start with a number. It is also SARG-able and will use an index if one exists.