delete query is giving error but same condition works in select

Question

delete from T_SEARCH_TABLES
where TABLE_NAME = 'D_VENDOR'
      and SEARCH_ID in(select distinct htvf.Value
                       from T_RESX_VALUES
                            cross apply dbo.func_split(T_RESX_VALUES.[key],'.') as htvf
                       where T_RESX_VALUES.value like '%Invoices - Activity (%'
                              and htvf.Position = '2'
                              and T_RESX_VALUES.[KEY] like 'SearchName.%'
                      )
      and SEARCH_TABLE_ID not in(select SEARCH_TABLE_ID
                                 from T_SEARCH_COLUMNS
                                );

Is giving error:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'SearchName' to data type int.

But

select *
from T_SEARCH_TABLES
where TABLE_NAME = 'D_VENDOR'
      and SEARCH_ID in(select distinct htvf.Value
                        from T_RESX_VALUES
                            cross apply dbo.func_split(T_RESX_VALUES.[key],'.') as htvf
                        where T_RESX_VALUES.value like '%Invoices - Activity (%'
                              and htvf.Position = '2'
                              and T_RESX_VALUES.[KEY] like 'SearchName.%'
                       )
      and SEARCH_TABLE_ID not in(select SEARCH_TABLE_ID
                             from T_SEARCH_COLUMNS
                            );

is working fine what is the problem in it ?

Well, my first idea would be that T_RESX_VALUES.[KEY] is actually an integer. I'm not sure why it is a problem in delete and not in select, though — Piotr J.
– Piotr J., Commented Feb 17, 2017 at 9:52
What happens with the select version if you uncomment out the last not in condition so your queries are actually searching for the same thing? — iamdave
– iamdave, Commented Feb 17, 2017 at 10:00

jambonick · Accepted Answer · 2017-02-17 10:16:24Z

2

Are you sure that at both DELETE and SELECT statements the first conditions of your inner WHERE CLAUSE

(T_RESX_VALUES.value like '%Invoices - Activity (%'and htvf.Position = '2')

were met?

Let me explain

I have run these two statements at my DB

select * from customers where cust_id = 0 and Cust_Name = 1

select * from customers where cust_id = 816171 and Cust_Name = 1

The Cust_Name column is of type varchar. My table contains one customer with ID = 816171 and Cust_Name = 'Mike' but no customers with ID = 0.

I got the following results: The first query run ok but returned no rows

The second query returned the following error:

Conversion failed when converting the varchar value 'Mike' to data type int.

It seems that sql-server doesn't try even to commit the conversion if the first condition is not met.

Actually the reason is that SQL doesn't always executes checks of where clauses in same order, the engine itself chooses every time the most optimized order.

You can read here more: http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/

edited Feb 17, 2017 at 10:16

answered Feb 17, 2017 at 10:10

jambonick

7161 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user7334823 Over a year ago

Yes i am sure my condition meets the data requirement

jambonick Over a year ago

does your Select statement returns any values?

user7334823 Over a year ago

Yes it does as expected

jambonick Over a year ago

Try to run the SELECT statement without the and 'T_RESX_VALUES.[KEY] like 'SearchName.%'' condition. Does it return the same or more rows?

user7334823 Over a year ago

that is important to include for delete

|

Collectives™ on Stack Overflow

delete query is giving error but same condition works in select

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related