英文:
Update MariaDB column when provided value is not empty string
问题
我目前正在开发一个使用Golang编写的应用程序，它与MariaDB数据库进行通信。我的服务将接收一个*User对象，并尝试更新匹配的记录。
例如：
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
}
然而，我不想更新未提供的字段。我希望实现更接近以下方式的更新，即如果提供的值是空字符串，则不更新该字段：
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=?
我了解到SQL允许使用CASE语句进行更新，但我没有找到一种在Exec()中多次使用同一变量的方法。
英文:
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
得分: 2
像这样
更新用户
设置名字 = 如果?为空则使用原来的名字，否则使用?
或者只传递每个参数一次
更新用户
设置名字 = 如果参数不为空则使用参数，否则使用原来的名字
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论