Multiple IN Conditions on a DELETE FROM query throwing a #245 Conversion Type Error

Question

I have a table setup like the following:

Parameters
╔═══╦═════════╦════════╗
║ID ║ Name    ║ Value  ║
╠═══╬═════════╬════════╣
║ 7 ║ first   ║  0     ║
║ 7 ║ second  ║ -1     ║
║ 7 ║ third   ║ -1     ║
╚═══╩═════════╩════════╝

It contains more rows, but I only want to delete the ones listed above. I have made the following query below to perform this action, but when you add a 3rd value to the IN condition for name I get:

ErrorNumber 245 - "Conversion failed when converting the varchar value to data type int."

DELETE FROM Parameters
    WHERE 
        ID = 7 AND 
        Name IN ('first', 'second', 'third') AND 
        Value IN (0, -1)

If I delete any of the 3 names making the IN condition 1 or 2 names it runs fine, but I need the third row to be deleted in the same query. What can I do to accomplish this?

score 2 · Accepted Answer · answered Sep 28 '17 at 20:34

2

Clearly, either id or value is a string. SQL Server has to decide what type to use. If a string is compared to a number, then a number is used. If another row has a bad number, then you get a type conversion error.

You should use the proper types for comparison. If I had to guess, it would be value:

DELETE FROM Parameters
WHERE 
    ID = 7 AND 
    Name IN ('first', 'second', 'third') AND 
    Value IN ('0', '-1');

You can put single quotes around numeric constants such as 7. I discourage it, because mis-using types is a bad coding habit.

answered Sep 28 '17 at 20:34

Gordon Linoff

1,242,037
58
646
786

That would be my guess too – Kevin Sep 28 '17 at 20:37
@Gordon you were right, it was the value column. Thank you so much for the quick response! I will accept the answer as soon as the option becomes available. – Dr Pepper Sep 28 '17 at 20:42

Multiple IN Conditions on a DELETE FROM query throwing a #245 Conversion Type Error

1 Answers1