SQL 字符串中的 MySql 更新错误

Question

不知道为什么，但 MySQL 总是因语法问题而出错。我在下面的 SQL 语句中没有看到任何问题。但在这个简单的更新语句期间它一直出错。

public static void updateTable1(Car cars){
    try ( Connection con = JdbcUtil.getConnection()) {
        String sql = "UPDATE CARS_TABLE T SET TUNING_PERFORM = "
                + "SELECT R.BORE * R.STROKE  "
                + "FROM ENGING_DERIVE R "
                + "WHERE T.ID = R.ID;"
                ;

        try (Statement st = con.createStatement()) {
            System.out.println(sql);
            int res = st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);

        }
    } catch (Exception e) {
        log.error("ERROR", e);
        e.printStackTrace();
    }
}

mysql语法错误语句为:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT R.BORE * R.STROKE  FROM ENGING_DERIVE R ' at line 1

Answer 1

它不是那样工作的。最好使用 JOIN 代替

UPDATE CARS_TABLE T 
JOIN ENGING_DERIVE R ON T.ID = R.ID
SET T.TUNING_PERFORM = R.BORE * R.STROKE