duankuiyuant3940
duankuiyuant3940
2017-01-14 05:57

提供的值不为空字符串时更新MariaDB列

已采纳

I am currently working on a Golang application that talks to a MariaDB database. My service will take in a *User and attempt to update the matching record.

For example:

func (d *database) UpdateUser(user *User) error {
    stmt, err := d.Prepare(`UPDATE Users
        SET FirstName=?, LastName=?, Email=?, Address1=?, Address2=?,
            City=?, State=?, Country=?, PostalCode=?
        WHERE Id=?`)
    if err != nil {
        return err
    }

    _, err := res, err := stmt.Exec(user.FirstName, user.LastName, user.Email,
        user.Address1, user.Address2, user.City, user.State, user.Country, 
        user.PostalCode, user.Id)
    return err
}

However, I do not want to update fields that aren't supplied. I am looking for something closer to this, where it would not update the value if the provided one is an empty string:

`UPDATE Users
 SET FirstName=? OR FirstName, LastName=? OR LastName, Email=? OR Email, 
     Address1=? OR Address1, Address2=? OR Address2, City=? OR City, 
     State=? OR State, Country=? OR Country, PostalCode=? OR PostalCode
 WHERE Id=?`

I have seen that SQL allows for updating using a CASE statement, but I do not see a way to use this without providing the same variable to the Exec() several times.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • duancha1065 duancha1065 4年前

    Like this

    UPDATE Users
    SET FirstName = case when ? is null then FirstName else ? end
    

    or to pass each parameter just once

    UPDATE Users
    SET FirstName = coalesce(?, FirstName)
    
    点赞 评论 复制链接分享