使用JDBC在MySQL中插入select [英] insert in select in MySQL with JDBC

问题描述

我想将一行中的值插入另一行，这是我的代码:

I would like to have a value from a row inserted into an other row here is my code:

static void addVipMonth(String name) throws SQLException
{
    Connection conn = (Connection) DriverManager.getConnection(url, user, pass);
    PreparedStatement queryStatement = (PreparedStatement) conn.prepareStatement("INSERT INTO vips(memberId, gotten, expires) " +
            "VALUES (SELECT name FROM members WHERE id = ?, NOW(), DATEADD(month, 1, NOW()))"); //Put your query in the quotes
    queryStatement.setString(1, name);
    queryStatement.executeUpdate(); //Executes the query
    queryStatement.close(); //Closes the query
    conn.close(); //Closes the connection
}

此代码无效.我该如何纠正?

This code is not valid. How do I correct it?

推荐答案

我收到错误17:28:46 [SEVERE] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MyS QL server version for the right syntax to use near ' NOW(), DATE_ADD( now(), INT ERVAL 1 MONTH )' at line 1 – sanchixx

I get an error 17:28:46 [SEVERE] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MyS QL server version for the right syntax to use near ' NOW(), DATE_ADD( now(), INT ERVAL 1 MONTH )' at line 1 – sanchixx

这是由于SELECT ..语句中的错误所致.
修改后的语句是:

It was due to error in SELECT .. statement.
Modified statement is:

INSERT INTO vips( memberId, gotten, expires )  
   SELECT name, NOW(), DATE_ADD( now(), INTERVAL 1 MONTH )
    FROM members WHERE id = ?

---

1. 当inserting与select一起使用时，您不需要VALUES关键字.
2. 您使用了错误的DATEADD函数语法.正确的语法是Date_add( date_expr_or_col, INTERVAL number unit_on_interval).

1. You don't require VALUES key word when inserting with a select.
2. You used a wrong DATEADD function syntax. Correct syntax is Date_add( date_expr_or_col, INTERVAL number unit_on_interval).

您可以尝试按以下更正的方式插入插入语句:

You can try your insert statement as corrected below:

INSERT INTO vips( memberId, gotten, expires )  
   SELECT name FROM members
     WHERE id = ?, NOW(), DATE_ADD( now(), INTERVAL 1 MONTH )

引用:

1. 插入...选择语法
2. DATE_ADD (日期，INTERVAL expr单位)

这篇关于使用JDBC在MySQL中插入select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！