Is this right?

26 Aug 2005 9:31 PM

WhiteJul

Please check my sintax for MS SQL 2000 query:

Update Geo_Location
Set Driver_Route# = '12101', Pedestrian_Route# = '15201'
Where MAIN_LOCATION = 'Q28-41'
    OR MAIN_LOCATION = 'Q29-41'
    OR MAIN_LOCATION = 'Q30-41'
    OR MAIN_LOCATION = 'Q30-42'
    OR MAIN_LOCATION = 'Q29-42'
    OR MAIN_LOCATION = 'Q28-42'
and mid(dRIVER_Route# ,1 ,1) = '1'

26 Aug 2005 9:39 PM

Anith Sen

First, use QA to check syntax and use SQL Server Books Online for learning
them.

Next, there is no MID() function in t-SQL. Assuming this is comparable to
the mid function in visual basic, you can use SUBSTRING or LEFT function
instead. Again, details of these functions and examples can be found in SQL
Server Books Online.

Finally, you can simplify your statement further as:

UPDATE Geo_Location
   SET Driver_Route = '12101',
       Pedestrian_Route = '15201'
WHERE Main_Location IN (
       'Q28-41', 'Q29-41', 'Q30-41', 'Q30-42', 'Q29-42', 'Q28-42' )
   AND LEFT( Driver_Route, 1 ) = '1' ;

--
Anith

29 Aug 2005 10:40 PM

WhiteJul

Show quote

"Anith Sen" wrote:

> First, use QA to check syntax and use SQL Server Books Online for learning
> them.
>
> Next, there is no MID() function in t-SQL. Assuming this is comparable to
> the mid function in visual basic, you can use SUBSTRING or LEFT function
> instead. Again, details of these functions and examples can be found in SQL
> Server Books Online.
>
> Finally, you can simplify your statement further as:
>
> UPDATE Geo_Location
>    SET Driver_Route = '12101',
>        Pedestrian_Route = '15201'
> WHERE Main_Location IN (
>        'Q28-41', 'Q29-41', 'Q30-41', 'Q30-42', 'Q29-42', 'Q28-42' )
>    AND LEFT( Driver_Route, 1 ) = '1' ;
>
> --
> Anith
>
>
>

How would the query be using SUBSTRING ?
I run:

UPDATE Geo_Location
    SET Driver_Route = '12101',
        Pedestrian_Route = '15201'
WHERE Main_Location IN (
        'Q28-41', 'Q29-41', 'Q30-41', 'Q30-42', 'Q29-42', 'Q28-42' )
    AND SUBSTRING ( Driver_Route, 1 ) = '1' ;

and I got this:
Server: Msg 174, Level 15, State 1, Line 6
The substring function requires 3 arguments.

30 Aug 2005 2:06 PM

Anith Sen

Did you check SQL Server Books Online to see how to use the SUBSTRING
function?

--
Anith

26 Aug 2005 9:49 PM

ML

It's OK, but can be smiplified like this:

Update Geo_Location
Set Driver_Route# = '12101', Pedestrian_Route# = '15201'
Where MAIN_LOCATION in ('Q28-41', 'Q29-41', 'Q30-41', 'Q30-42', 'Q29-42',
'Q28-42')
and mid(dRIVER_Route# ,1 ,1) = '1'

BTW: how many values reside in the dRIVER_Route# column? Or, to put it
another way, why do you have to parse a single character from there? Legacy
Hell?

ML

26 Aug 2005 9:57 PM

ML

Damn! Damn! Damn!

Of course it's wrong! There's no MIDfunction in SQL!

And the AND operator has precedence over the OR operator, so you need
brackets around all the OR comparisons.

And I need glasses...

ML