MySQL - using a prepared statement to copy a row in the same table and include static values throws an error

Question

I am trying to use a prepared statement to copy one row to a new row (INSERT)in the same table and include some static values. However, I get the following error:

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

My input is:

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

My code is:

    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

You have parens around the field list in your SELECT! Remove them.

The issue is that SELECT expects a list of (individual) columns, but you are passing a multi-column value.

SELECT x, y, z sees x, y and z as three values each consisting of one column, yet you are passing SELECT (x, y, z) which is interpreted as one value with three columns, hence the error.

By the way, the parens around the whole SELECT are superfluous as well, this time because INSERT INTO table (columns) SELECT ... is a recognized syntax construct in itself.

Corrected code:

    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 = ?;");