Incorrect syntax near the keyword 'From'.

Question

Incorrect syntax near the keyword 'From'.

barryocteau

Old Hand

Points: 374
More actions
April 4, 2014 at 10:23 am

#306061

I get this error running this update:
PROPERTIES (table)
MapParam (column, string)
Want to prepend record string with "lat:" for all records that contain "44."
Change string in record where "44.012345, -78.012345"
becomes new string "lat:44.012345, -78.012345" for all strings beginning with "44."
============
UPDATE PROPERTIES
SET MapParam = REPLACE(MapParam, '44.', 'lat:44.')
WHERE MapParam LIKE '44.%'
============
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

You need to include a from clause in your update.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

djj SSCoach Points: 18831 More actions · Answer 2

Jason, I do not think that is the problem. However that might fix it.

I did a quick test

create table Props (mapparam varchar(20));

INSERT INTO Props values (44.01);

select mapparam from Props;

update Props

SET mapparam = REPLACE(mapparam, '44.', 'lat:44.')

where mapparam like '44.%';

select mapparam from Props;

works fine.

I did wonder about OP using the word "string"

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

djj (4/4/2014)
Jason, I do not think that is the problem. However that might fix it.
I did a quick test
create table Props (mapparam varchar(20));
INSERT INTO Props values (44.01);
select mapparam from Props;
update Props
SET mapparam = REPLACE(mapparam, '44.', 'lat:44.')
where mapparam like '44.%';
select mapparam from Props;
works fine.
I did wonder about OP using the word "string"

This works on the provided sample data but it does not work in all cases. If you have the string '44.' more than once in the value your code will replace all of them.

Try adding this row and running your code. You will see what I mean.

INSERT INTO Props values ('44.012345, -44.012345');

Instead of REPLACE we can use STUFF which will work fine on this type of value.

update Props

SET mapparam = STUFF(mapparam, 1, 0, 'lat:')

where mapparam like '44.%';

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

barryocteau Old Hand Points: 374 More actions · Answer 4

Thanks Folks..the query was indeed valid but for reasons unknown it wouldn't function through a certain web module (third party) .net app.

Different module and tweaked connection string..as well as through SQL Server Management..and all is well.

I believe the module I was using was defined for 'SELECT' statements only..and hence looking for requisite 'FROM'

Thanks again ..resolved.

Barry

barryocteau Old Hand Points: 374 More actions · Answer 5

Thanks for the insight on STUFF ..totally over looked that.

Cheers

Barry