Is this right?

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'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|||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|||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|||
"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 SQ
L
> 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.|||Did you check SQL Server Books Online to see how to use the SUBSTRING
function?
Anith