错误:无法在准备好的语句中插入多个命令 [英] Error: cannot insert multiple commands into a prepared statement

问题描述

我正在尝试将一行从一个表移动到另一个表.

I am trying to move a row from one table to another.

问题是，如果我将两个查询放在一起，我会收到错误:无法将多个命令插入到准备好的语句中".我能做什么?

The problem is that if I put both queries together, I get "error: cannot insert multiple commands into a prepared statement". What can I do?

exports.deletePost = function(id) {
    return db.query(`INSERT INTO deletedjobs
    SELECT *
    FROM jobs
    WHERE id = $1;

    DELETE FROM jobs WHERE id = $1;`, [id]).then(results => {
        console.log("succesfull transfer");
        return results.rows[0];
    });
};

推荐答案

您还可以使用 WITH ... AS 子句 (https://www.postgresql.org/docs/9.1/queries-with.html) 一次性执行两个查询.所以它可能看起来像这样:

You can also use a WITH ... AS clause (https://www.postgresql.org/docs/9.1/queries-with.html) to execute both queries in one go. So it could look something like that:

exports.deletePost = function(id) {
return db.query(`
  WITH 
    A AS (SELECT * FROM jobs WHERE id = $1), 
    B AS (INSERT INTO deletedjobs FROM A),
    DELETE FROM jobs WHERE id IN (SELECT id FROM A);
`, [id]).then(results => {
    console.log("succesfull transfer");
    return results.rows[0];
});
};

这篇关于错误:无法在准备好的语句中插入多个命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！