In my experience you cannot do what you are trying do with that SQL. You
cannot concatenate fields the way that you are doing. SQL updates are in the
format of:
Update <database>
Set <column> = <somevalue>,
<column> = <somevalue>
This may work.
Update Wawa_DB
Set CityStateZip = City + ' ' + State + ' ' + PostalCode
Ray Thompson
Tau Beta Pi Headquarters
865-546-4578
www.tbp.org
Hardware eventually fails. Software eventually works.
Well, software does not always eventually work.
-----Original Message-----
From: Denise Zuverink [mailto:[email protected]]
Sent: Monday, December 10, 2012 8:42 AM
To: sql
Subject: RE: Update data in a field from concatenated data
UPDATE Wawa_DB.CityStateZIP,
Wawa_DB.city || ' ' || Wawa_DB.state || ' ' || Wawa_DB.postalcode FROM
The message that is returned is...
[SQL] UPDATE Wawa_DB.CityStateZIP, Wawa_DB.city || ' ' ||Wawa_DB.state || '
'|| Wawa_DB.postalcode FROM Wawa_DB
[Err] 1 - near ",": syntax error
Wawa_DB
-----Original Message-----
From: Raymond Thompson [mailto:[email protected]]
Sent: Saturday, December 08, 2012 5:57 PM
To: sql
Subject: RE: Update data in a field from concatenated data
Ah, I see. There is a space between those single quote marks. I don't see
why would you have would not work. The spaces should not present any issues.
More information is going to be needed such as providing the query that is
failing.
Ray Thompson
Tau Beta Pi Headquarters
865-546-4578
www.tbp.org
Hardware eventually fails. Software eventually works.
Well, software does not always eventually work.
-----Original Message-----
From: Denise Zuverink [mailto:[email protected]]
Sent: Friday, December 07, 2012 3:25 PM
To: sql
Subject: RE: Update data in a field from concatenated data
Sorry,
City State Zip (space between each field) is what is being returned, please
see below for an example of the returned query.
Folsom PA 19033-1610
-----Original Message-----
From: Raymond Thompson [mailto:[email protected]]
Sent: Friday, December 07, 2012 3:09 PM
To: sql
Subject: RE: Update data in a feild from concatenated data
I am going to guess because the update query has not been provided.
What you are returning from your query is something like this:
city'state'postalcode
When you try to update your code may look something like this:
<cfset MyData= QueryName.YourQueryResult> Update database set
field='#mydata#'
The single quotes are causing the problem in my guess.
Do something like this:
<cfset MyData=QueryName.YourQueryResult> Update database set field=
<cfqueryparam value="#MyData#"
cfsqltype="CF_SQL_VARCHAR">
That way the single quotes will not matter as the data does not become part
of the query but is instead passed as a query parameter.
Ray Thompson
Tau Beta Pi Headquarters
865-546-4578
www.tbp.org
Hardware eventually fails. Software eventually works.
Well, software does not always eventually work.
-----Original Message-----
From: Denise Zuverink [mailto:[email protected]]
Sent: Friday, December 07, 2012 2:11 PM
To: sql
Subject: Update data in a feild from concatenated data
I am having a mental block and was hoping someonce could help me. I am
trying to update data in a feild from concatenated data. This is the code I
wrote and it is a returning a query exactly like I want to see it in the
feild but replace/update just does not seem to be working.
SELECT Wawa_DB.city || ' ' ||Wawa_DB.state || ' '|| Wawa_DB.postalcode FROM
Wawa_DB
I am using Navicat
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3466
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
