INSERT INTO ... SELECT "unknown error"

[Paul Lahaie]

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.

<CFSET evalid = 1 />
<CFQUERY name="getFormGroups" dataSource="#Request.Site.DSN#">
        SELECT num, name
          FROM form1groups
         WHERE form1id = #formid#
         ORDER BY num
</CFQUERY>

<CFLOOP query="getFormGroups">
        <CFQUERY name="insertEvalGroup" dataSource="#Request.Site.DSN#">
                INSERT INTO eval1groups(eval1groupid,eval1id,num,name) VALUES(0,#evalid#,#num#,'#name#')
        </CFQUERY>
       
        <CFQUERY name="getEvalGroupId" dataSource="#Request.Site.DSN#">
                SELECT LAST_INSERT_ID() AS groupid
        </CFQUERY>
       
        <CFQUERY name="insertEvalItems" dataSource="#Request.Site.DSN#">
                INSERT INTO eval1items(eval1itemid,eval1groupid,num,type,indent,question)
                     (SELECT 0, #getEvalGroupId.groupid#, num, type, indent, question
                       FROM form1items
                      WHERE form1groupid = #getFormGroups.form1groupid#)
        </CFQUERY>
</CFLOOP>

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:3200
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Adrian Moreno]

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" <paul.lahaie@...>
Sent: Friday, March 13, 2009 3:47 PM
To: "sql" <sql@...>
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=17837.14401.6