I have a table that has approximately 1 billion records. I have a staging table of duplicate structures that feeds that table. I have a query that deletes from the production table the rows about to be sent. It looks something like this:

DELETE tbl1
    FROM tbl1
    INNER JOIN tbl2
        ON (tbl1.INPATIENT_DATA_ID = tbl2.INPATIENT_DATA_ID)

INPATIENT_DATA_ID is a varchar(50) in both tables. When I try to delete the records. I get this error:

Error converting data type varchar to numeric.

Here is a list of things I have tried

1. Using CAST/CONVERT on the INPATIENT_DATA_ID field in SELECT returns all rows
2. Using TRY_CAST/TRY_CONVERT on either side of the join produces the same error
3. Using TRY_CAST/TRY_CONVERT on the INPATIENT_DATA_ID field in SELECT returns all rows with no NULLS