javascript - 简单更新 sqlite3 数据库
问题描述
我想以某种方式更新我的数据,以保留其值为null的属性, 就像它们在表中一样,并且只更改那些具有新值作为参数的属性。
这是我的请求,当我的对象中有空值时它会向我发送错误:
/* THE OBJECT */
let chick = { ID: 3, name: null, lastname: 'soup', phone: '60-58-22-12', password: null , mail: 'chicken@gmail.com' }
/* UPDATE REQUEST */
this.state._stmt = db.prepare("UPDATE user SET
name = CASE WHEN (? = NULL) THEN name ELSE ? END ,
lastname = CASE WHEN (? = NULL) THEN lastname ELSE ? END ,
phone= CASE WHEN (? = NULL) THEN phone ELSE ? END ,
password = CASE WHEN (? = NULL) THEN password ELSE ? END,
mail = CASE WHEN (? = NULL) THEN mail ELSE ? END
WHERE ID=? ;");
this.state._stmt.run(obj.name, obj.name, obj.lastname, obj.lastname , obj.phone, obj.phone, obj.password, obj.password, obj.mail, obj.mail, obj.ID);
/
/* 我得到的错误是:*/
[Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: user.name
Emitted 'error' event on Statement instance at:
] {
errno: 19,
code: 'SQLITE_CONSTRAINT'
}
解决方案
我找到了两种方法:
使用更短的合并
this.state._stmt = db.prepare("UPDATE user SET
name = COALESCE(?, name) ,
lastname = COALESCE(?, lastname) ,
phone= COALESCE(?, phone) ,
password = COALESCE(?, password),
mail = COALESCE(?, mail)
WHERE ID=? ;
");
保留我的请求但更改条件 NULL->'null'
UPDATE user
SET name = CASE
WHEN ? != 'null' THEN ?
ELSE name END
WHERE ID= ? ;