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?
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.