Update data in a feild from concatenated data

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Update data in a feild from concatenated data

Denise Zuverink

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:3460
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Update data in a feild from concatenated data

Raymond Thompson

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:[hidden email]]
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:3461
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Update data in a feild from concatenated data

George Gallen-3

Depends on the database

http://www.1keydata.com/sql/sql-concatenate.html

George

At least from a SQL point of view, as I didn't think CF came into play with
your question.

-----Original Message-----
From: Raymond Thompson
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:[hidden email]]
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:3463
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
Loading...