0

I want to update my sqlite table and I use a request with conditionals my purpose is to set the name to the new value when the value is not null and keep the old value if the new value is null . I would like anybody check my request to see if there is something wrong cause I'm getting errors

The request below :

UPDATE user SET name = CASE WHEN (? = NULL) THEN user.name ELSE ? END  WHERE ID=? ;

The error I'm getting here

SQLITE_CONSTRAINT: NOT NULL constraint failed: user.name
Emitted 'error' event on Statement instance at:
] {
  errno: 19,
  code: 'SQLITE_CONSTRAINT'
}
  • 1
    You have not told us what the errors are. And not read the documentation. – CL. May 28 at 9:21
  • I read it but it is not simple to understand for me so I did as I have understood – Christopharus May 28 at 9:28
  • NULL means 'unknown'. If you compare some value with null (? = NULL) - what result this must give? we compare something and unknown something - the result is unknown, i.e. the result is NULL... which is always treated as FALSE. So ELSE variant is executed/calculated always... – Akina May 28 at 13:21
  • But if instead of (?=NULL) I compare ( ? != NULL) it could permit me to have what I want ? – Christopharus May 28 at 14:14
  • if instead of (?=NULL) I compare ( ? != NULL) then you again will obtain NULL treated as FALSE in any case. Or IS [NOT] NULL, or null-safe compare operator <=> (dependent by the logic). – Akina May 28 at 15:21
0
UPDATE user 
SET name = COALESCE(?, name) -- name to add if the value is not null
WHERE ID = ?                 -- id value of the record which must be updated
;
| improve this answer | |
  • I tried your request but it doesn't work . This is what I want : if ? is null keep old value and if ? not null change the old value – Christopharus May 28 at 14:07
  • That would then be COALESCE(?, name) – mustaccio May 28 at 14:25
  • Right this time it works – Christopharus May 28 at 14:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.