MySQL -使用准备好的语句复制同一表中的行，并包含静态值将引发错误

Question

我试图使用预先准备的语句将一行复制到同一表中的新行(INSERT)，并包含一些静态值。但是，我得到以下错误：

SQLException in copyProgram: java.sql.SQLException: Operand should contain 1 column(s)

我的意见是：

prId: 4 accountID: 50 newFromDate: 2020-04-27 newToDate: 2020-04-28

我的代码是：

    String insertQry = ("INSERT INTO at_program "
            + "(acc_id, pr_name, pr_start_date, pr_start_time, pr_end_date, pr_end_time, "
            + "pr_cost_pp, pr_woodbeads, pr_special, pr_joeys, pr_cubs, "
            + "pr_scouts, pr_venturers, pr_rovers, pr_leaders, pr_family, "
            + "pr_swimming, pr_pioneering, pr_archery, pr_canoe, pr_bushwalking, "
            + "pr_4wd, pr_abseiling, pr_snorkelling, pr_boating, "
            + "pr_rock_climbing, pr_caving, pr_branch_instructions, pr_policies_information, "
            + "pr_whs, pr_other, pr_notes) "
            + " (SELECT (?, pr_name, ?, pr_start_time, ?, pr_end_time, "
                    + "pr_cost_pp, pr_woodbeads, pr_special, pr_joeys, pr_cubs, "
                    + "pr_scouts, pr_venturers, pr_rovers, pr_leaders, pr_family, "
                    + "pr_swimming, pr_pioneering, pr_archery, pr_canoe, pr_bushwalking, "
                    + "pr_4wd, pr_abseiling, pr_snorkelling, pr_boating, "
                    + "pr_rock_climbing, pr_caving, pr_branch_instructions, pr_policies_information, "
                    + "pr_whs, pr_other, pr_notes) "
            + " FROM at_program "
            + " WHERE pr_id = ?);"); 

 ps = c.prepareStatement(insertQry, Statement.RETURN_GENERATED_KEYS);
            // Create a statement and execute the query on it               
            ps.setString(1, accountID);
            ps.setString(2, fromDate);
            ps.setString(3, toDate);
            ps.setString(4, prID);

            ps.executeUpdate();

Answer 1

在您的SELECT中，您在字段列表周围有父母！把他们移走。

问题是SELECT需要一个(单独)列的列表，但是您要传递一个多列值。

SELECT x, y, z将x、y和z看作三个值，每个值由一个列组成，但是您正在传递SELECT (x, y, z)，它被解释为一个有三列的值，因此出现了错误。

顺便说一句，整个SELECT周围的双亲也是多余的，这一次是因为INSERT INTO table (columns) SELECT ...本身就是一个公认的语法结构。

修正代码：

    String insertQry = ("INSERT INTO at_program "
            + "(acc_id, pr_name, pr_start_date, pr_start_time, pr_end_date, pr_end_time, "
            + "pr_cost_pp, pr_woodbeads, pr_special, pr_joeys, pr_cubs, "
            + "pr_scouts, pr_venturers, pr_rovers, pr_leaders, pr_family, "
            + "pr_swimming, pr_pioneering, pr_archery, pr_canoe, pr_bushwalking, "
            + "pr_4wd, pr_abseiling, pr_snorkelling, pr_boating, "
            + "pr_rock_climbing, pr_caving, pr_branch_instructions, pr_policies_information, "
            + "pr_whs, pr_other, pr_notes) "
            + " SELECT ?, pr_name, ?, pr_start_time, ?, pr_end_time, "
                    + "pr_cost_pp, pr_woodbeads, pr_special, pr_joeys, pr_cubs, "
                    + "pr_scouts, pr_venturers, pr_rovers, pr_leaders, pr_family, "
                    + "pr_swimming, pr_pioneering, pr_archery, pr_canoe, pr_bushwalking, "
                    + "pr_4wd, pr_abseiling, pr_snorkelling, pr_boating, "
                    + "pr_rock_climbing, pr_caving, pr_branch_instructions, pr_policies_information, "
                    + "pr_whs, pr_other, pr_notes "
            + " FROM at_program "
            + " WHERE pr_id = ?;");