1. From your last query:
WHERE form1groupid = #getFormGroups.form1groupid#
I don't see that you selected form1groupid from form1groups. Typo when posting?
2. You can skip the middle query from your inserts and just use LAST_INSERTID()
in the last query:
<CFQUERY name="insertEvalItems" dataSource="#Request.Site.DSN#">
INSERT INTO eval1items
(
eval1itemid,
eval1groupid,
num,
type,
indent,
question
)
SELECT
0,
LAST_INSERT_ID(),
num,
type,
indent,
question
FROM
form1items
WHERE
form1groupid = #getFormGroups.form1groupid#
</CFQUERY>
3. Are you incrementing evalid?
-- Adrian
----------------------------------------
From: "Paul Lahaie" <[email protected]>
Sent: Friday, March 13, 2009 3:47 PM
To: "sql" <[email protected]>
Subject: INSERT INTO ... SELECT "unknown error"
Platform: CentOS 5.2 x86-64, ColdFusion 8 DE, MySQL 5.0.45
I'm trying to use INSERT INTO ... SELECT and I'm getting a HY000 / Error code
1105 from MySQL.
SELECT num, name
FROM form1groups
WHERE form1id = #formid#
ORDER BY num
INSERT INTO eval1groups(eval1groupid,eval1id,num,name)
VALUES(0,#evalid#,#num#,'#name#')
SELECT LAST_INSERT_ID() AS groupid
INSERT INTO
eval1items(eval1itemid,eval1groupid,num,type,indent,question)
(SELECT 0, #getEvalGroupId.groupid#, num, type, indent,
question
FROM form1items
WHERE form1groupid = #getFormGroups.form1groupid#)
If I replace the CFQUERY with a CFOUTPUT, I can cut & paste the resulting
INSERTs into Navicat to the MySQL server and everything runs as it should.
Is this a limitation of ColdFusion or the ColdFusion / MySQL bridge? I've
tried the SELECT w/o parentheses and get the same error.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3201
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
