如何使用节点在 sqlite 数据库中的多个条目值中使用更新方法.js

Question

我是sqlite和node.js的新手，想知道是否可以在db.run命令中处理多个条目。假设您有多个条目，如下所示：

{"message":"success",
"data":[
{"id":1,"name":"user_delete",
"email":"[e-mail]",
"password":"a66abb5684c45962d887564f08346e8d",
"status":"pending_deleted"},
{"id":3,"name":"mikey",
"email":"[e-mail]",
"password":"b18f58b85575fc3fb8b9189445d1071f",
"status":"pending_deleted"}]}

每当用户的状态为"0"时，我都想使用UPDATE方法；pending_deleted"；并将状态更改为已删除。我看到了它在一个条目上使用而在多个条目上不使用的例子。

我在一个js文件中设置了我的数据库，如下所示：

let sqlite3 = require('sqlite3').verbose()
let md5 = require('md5')
const DBSOURCE = ":memory:"
let db = new sqlite3.Database(DBSOURCE, (err) => {
if (err) {
// Cannot open database
console.error(err.message)
throw err
}else{
console.log('Connected to the SQLite database.')
db.run(`CREATE TABLE user (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name text, 
email text UNIQUE, 
password text, 
status text,
CONSTRAINT email_unique UNIQUE (email)
)`,
(err) => {
if (err) {
// Table already created
console.log("Table already created");
}else{
// Table just created, creating some rows
var insert = 'INSERT INTO user (name, email, password, status) VALUES (?,?,?,?)'
db.run(insert, ["user_delete","[e-mail]",md5("admin123456"), "pending_deleted"])
db.run(insert, ["user_no_delete","[e-mail]",md5("user123456"), "active"])
db.run(insert, ["mikey","[e-mail]",md5("mikey123"), "pending_deleted"])
}
});  
}
});

Answer 1

我能够利用更新查询来解决这个问题，该查询检查状态值为"；pending_deleted"；

app.post("/account_service/pending_to_deleted", (req, res, next) => {
db.run(
`UPDATE user SET 
status = "deleted"
WHERE status = "pending_deleted"`,
function (err, result) {
if (err){
res.status(400).json({"error": res.message})
return;
}
res.json({
message: "success",
})
});
});