首页 > 解决方案 > 简单更新 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'
}

标签: javascriptnode.jssqlite

解决方案


我找到了两种方法:

使用更短的合并

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= ? ;

推荐阅读