仅在值不为空的情况下，MySQL更新列 [英] MySQL update column only if value not empty where

问题描述

我有一个UPDATE查询，并使用Ajax，我想知道是否有任何值可以为空，我只能更新数据库中不为空的值.我不知道是否有可能使用if语句或检查某些内容以跳过空值.我知道我可以添加另一个表单元素，但只想知道是否还有其他解决方案.

I have an UPDATE query and using Ajax, I wanted to know if any value is empty can I only update the values that not empty in the database. I don't know if this is possible to have a if statement or something to check to skip the empty values. I know I can just add another form element but just wanted to know if there was another solution.

仅当数据是来自前端表单的POST时.如果数据不是POST，请不要更新Title ='.$ title.'

Only if the data is POST from front end form. If data not POST don't update this Title = '.$title .',

$id = $_POST['id'];
$title = "";
$description = $_POST['Description'];
$date = $_POST['Date'];

 $query = 'UPDATE user SET

  `id` = '.$id.', 
  `Title` = '.$title .', 
  `Description` = '.$description.', 
  `Date` = '.$date =.' 
  WHERE `id` = '.$id;

 $result = mysql_query($query) or die("<b>A fatal MySQL error occured</b>.<br />Query:  ".$query."<br />Error: (".mysql_errno().") ".mysql_error());

更新:这对我有用.谢谢卡里姆·达拉夫(Karim Daraf)

Update: This is what worked for me. Thanks Karim Daraf

$ query =更新用户设置 Title = Coalesce($ title，Title)等...

$query = " UPDATE user SET Title = Coalesce($title,Title ) etc...

推荐答案

与Coalesce一起尝试.

Try it with Coalesce .

   $query = " UPDATE user 
   SET 
 `Title`       = CASE WHEN `Title`='' or `Title` IS NULL THEN '$title' END, 
 `Description` = CASE WHEN `Description`='' Or `Description` IS NULL THEN '$description' END, 
  `Date`       = CASE WHEN `Date`='' Or Date` IS NULL THEN '$date' END
    WHERE `id` = '".$id."' ";

或:

  $query = " UPDATE user 
  SET 
 `id`         = Coalesce('$id''".$id."' , NULLIF(`id`,'')), 
`Title`       = Coalesce('$title''".$title."',NULLIF(`Title`,'') ) , 
`Description` = Coalesce('$description''".$description."' , NULLIF(`Description`,'') ) , 
 `Date`       = Coalesce('$date''".$date."',NULLIF(`Date`,'')) 
 WHERE `id` = '$id''".$id."' ";

这篇关于仅在值不为空的情况下，MySQL更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！