Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar bu
t
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first an
d
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the convert
!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!IsNumeric will return 1 for some strings that can't be converted to an
integer, but can be converted to other numeric datatypes, like '1E10', which
can be converted to a float, or '200,000.00', which can be converted to
money.
http://www.aspfaq.com/show.asp?id=2390 gives you a workaround.
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
> Hi there.
> I have an update statement that wishes to update certain fields depending
> on
> a column that may or may not contain numeric values (the field is varchar
> but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update
> statement
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
> and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen
> cases
> on another server where the update statement still fails due to
> non-numeric
> values.
> Q: Is there some server setting that would dictate whether all criteria in
> a
> WHERE clause will get executed? Or in which order they get executed? I.e:
> Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the
> convert!
> I just want to know whether this is down to my code or is I should look
> for
> some other issue.
> Any help would be appreciated!|||Hi
The query processor can choose to execute a query in any way it pleases.
Depending on statistics, indexes, processors and RAM, it might decide to
execute a different query plan.
Have you tried:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
FROM
MyTable
INNER JOIN
OtherTable
ON
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
WHERE
IsNumeric(MyTable.MaybeNumeric) = 1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"len" wrote:
> Hi there.
> I have an update statement that wishes to update certain fields depending
on
> a column that may or may not contain numeric values (the field is varchar
but
> may contain '001', '002' etc. For the WHERE clause, I need to convert the
> MaybeNumeric field into an integer BUT... I know the whole update statemen
t
> will fail if the value is non-numeric (due to the convert(integer,...)
> criteria).
> The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
> criteria in the WHERE clause - thinking that this will get executed first
and
> will exclude non-numeric rows.
> This seems to work ok on my server (those famous words!) but I've seen cas
es
> on another server where the update statement still fails due to non-numeri
c
> values.
> Q: Is there some server setting that would dictate whether all criteria in
a
> WHERE clause will get executed? Or in which order they get executed? I.e:
Is
> the following code reliable:
>
> update
> MyTable
> set
> MyTable.SomeField = OtherTable.SomeField
> from
> OtherTable
> where
> IsNumeric(MyTable.MaybeNumeric) = 1
> and
> OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
> *note: this is a simplified version of the statement - I do need the conve
rt!
> I just want to know whether this is down to my code or is I should look fo
r
> some other issue.
> Any help would be appreciated!|||Optimiser can choose whatever evaluation path it sees fit. Look for "short
circuit" in the page below and see if it helps:
http://msdn.microsoft.com/library/d...
heckitout.asp
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"len" <len@.discussions.microsoft.com> wrote in message
news:01E35557-6ED2-4824-A721-F4F00A9B820F@.microsoft.com...
Hi there.
I have an update statement that wishes to update certain fields depending on
a column that may or may not contain numeric values (the field is varchar
but
may contain '001', '002' etc. For the WHERE clause, I need to convert the
MaybeNumeric field into an integer BUT... I know the whole update statement
will fail if the value is non-numeric (due to the convert(integer,...)
criteria).
The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
criteria in the WHERE clause - thinking that this will get executed first
and
will exclude non-numeric rows.
This seems to work ok on my server (those famous words!) but I've seen cases
on another server where the update statement still fails due to non-numeric
values.
Q: Is there some server setting that would dictate whether all criteria in a
WHERE clause will get executed? Or in which order they get executed? I.e: Is
the following code reliable:
update
MyTable
set
MyTable.SomeField = OtherTable.SomeField
from
OtherTable
where
IsNumeric(MyTable.MaybeNumeric) = 1
and
OtherTable.DefinitelyNumeric = convert(integer, MyTable.MaybeNumeric)
*note: this is a simplified version of the statement - I do need the
convert!
I just want to know whether this is down to my code or is I should look for
some other issue.
Any help would be appreciated!|||A more detailed analysis of the "short circuit" feature in SQL Server
is written in the following article (also by Itzik Ben-Gan):
http://www.windowsitpro.com/Article...?ArticleID=9148
Razvan|||>> I have an update statement that wishes to update certain fields [sic] depending on
a column that may or may not contain numeric values (the field [sic] is varchar b
ut may contain '001', '002' etc. <<
Exactly how did you get this kind of problem in the first place?
You have a huge design problem and need to change your schema, not go
hunting for kludges. One of the reasons that I beat on people about
not calling a "column" a "field" is that a field (a file processing
concept) can hold anything; it gets its meaning from being read by a
host program. A relational column has ONE AND ONLY ONE domain which
has ONE AND ONLY ONE data type. It has meaning in and of itself, it
enforces its own integrity, it does not depend on a host program.
If this data element is used for computations, then it needs to be a
numeric. If it is a tag number, then you can use character types. You
do not mix things like this in an RDBMS.
Also, you might want to stop using the unpredictable proprietary UPDATE
syntax.|||Len's requirement is common enough in an ELT data staging scenario. For
conforming external data I would typically load to an "untyped" table
(NVARCHAR throughout) first, perform the necessary validation, then
load the valid data to another staging table with the correct datatyes.
Any UPDATE against the actual data in the target database utilizes the
correctly typed table. This avoids type conversions and errors and
ensures you maximize the benefit of indexing on the two tables.
David Portas
SQL Server MVP
--|||On Wed, 25 May 2005 04:44:03 -0700, len wrote:
(snip)
>The solution I've chosen is to have IsNumeric(MaybeNumeric)=1 as the first
>criteria in the WHERE clause - thinking that this will get executed first a
nd
>will exclude non-numeric rows.
Hi len,
As others have said: there's no guarantee.
You might wish to try this one:
UPDATE MyTable
SET MyTable.SomeColumn = OtherTable.SomeColumn
FROM OtherTable
WHERE OtherTable.DefinitelyNumeric =
CASE WHEN IsNumeric(MyTable.MaybeNumeric) = 1
THEN CONVERT(integer, MyTable.MaybeNumeric)
ELSE OtherTable.DefinitelyNumeric + 1
END
But do beware the gotchas with IsNumeric (see Jacco's post).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
