java - 同时插入和更新一个表

Question

我有两个名为“Trending”和“Questbank”的表。

我正在尝试插入趋势表并同时更新 questbank 表。以下是我的代码。

PreparedStatement stmt = connection.prepareStatement(
                    "INSERT INTO TRENDING ( TOPIC , CATEGORY , QUESTION , ANSWER , PROFILEPIC , TICK , URL ) VALUES( ?, ? , ? , ? , ? , now() , ? )  Returning ID AND UPDATE  QUESTBANK SET wasanswered = 'yes'  WHERE id = ?");
            stmt.setString(1, jsonObj.getString("topic"));
            stmt.setString(2, jsonObj.getString("category"));
            stmt.setString(3, jsonObj.getString("question"));
            stmt.setString(4, jsonObj.getString("answer"));
            stmt.setString(5, jsonObj.getString("profilepic"));
            stmt.setString(6, jsonObj.getString("url"));
            stmt.setInt(7, jsonObj.getInt("id"));

但是我收到以下错误

failureorg.postgresql.util.PSQLException: ERROR: syntax error at or near \"SET\"\n  Position: 170"

我怎样才能在插入一个表的同时更新另一个表？

Answer 1

您可以使用 CTE 执行此操作:

WITH i AS (
      INSERT INTO TRENDING ( TOPIC , CATEGORY , QUESTION , ANSWER , PROFILEPIC , TICK , URL )
          VALUES( ?, ? , ? , ? , ? , now() , ? ) 
          Returning ID 
     )
UPDATE QUESTBANK
    SET wasanswered = 'yes' 
    WHERE id IN (SELECT ID FROM i);