troubleshooting Question

Why does this query work in straight MySQL but not in CF?

Avatar of SiobhanElara
SiobhanElaraFlag for United States of America asked on
ColdFusion LanguageMySQL Server
2 Comments1 Solution113 ViewsLast Modified:
I'm trying to duplicate a row in a MySQL table and give it a new ID (which is the primary key column.) If I run the query directly through phpMyAdmin, it works. If I run it on my CF page, it gives me the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE tempEvent SET ID = NULL; INSERT INTO calendar SELECT * FROM tempEvent' at line 4

Do you know why this would be? (As a side note, I get the same error if I use the cfqueryparam tag or hardcode a value, and with/without semicolons.) Thanks!

<cfquery datasource="#APPLICATION.ds#" result="copiedEvent">
	CREATE TEMPORARY TABLE tempEvent 
		SELECT * FROM calendar 
		WHERE ID = <cfqueryparam value="#FORM.eventID#" cfsqltype="CF_SQL_INTEGER">;
	UPDATE tempEvent 
		SET ID = NULL;
	INSERT INTO calendar SELECT * FROM tempEvent;
	DROP TEMPORARY TABLE IF EXISTS tempEvent;
</cfquery>
ASKER CERTIFIED SOLUTION
Russ SuterSenior Software Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros