英文:
I want to update specific fields in PostgreSQL only if they are null
问题
我想要在PostgreSQL中的一行上更新字段，只有当字段为空时才进行更新。否则，我会覆盖行中已有的内容。
我的update看起来像这样：
UPDATE public.people
SET flag=$flag,
name=$name,
surname=$surname
WHERE id_dt=$id_dt;
你能帮忙吗？
英文:
I was looking to update fields on a row in PostgreSQL only if the fields were null. Otherwise I would overwrite what's already in the row.
My update looks as simple as this:
UPDATE public.people
SET flag=$flag,
name=$name,
surname=$surname
WHERE id_dt=$id_dt;
Can you help?
答案1
得分: 2
UPDATE public.people
SET flag=$flag,
name=coalesce(name,$name),
surname=coalesce(surname,$surname)
WHERE id_dt=$id_dt
Coalesce() 返回第一个非空值，所以如果name已经存在，它将返回name，否则返回$name（这是您的新值）。
英文:
Here you go:
UPDATE public.people
SET flag=$flag,
name=coalesce(name,$name),
surname=coalesce(surname,$surname)
WHERE id_dt=$id_dt
Coalesce() returns the first non-null value, so name if it's already there, or $name (that is your new value)
答案2
得分: 0
BEGIN;
启动事务;
UPDATE public.people
更新 public.people 表
SET flag = $flag,
设置 flag 为 $flag，
name = $name
name 为 $name
WHERE id_dt=$id_dt
WHERE id_dt = $id_dt
AND name IS NULL;
并且 name 为空；
UPDATE public.people
更新 public.people 表
SET flag = $flag,
设置 flag 为 $flag，
surname = $surname
surname 为 $surname
WHERE id_dt=$id_dt
WHERE id_dt = $id_dt
AND surname IS NULL;
并且 surname 为空；
COMMIT;
提交事务；
If you go with the coalesce option like stated in a simpling answer, you can still add a where name is null or surname is null clause to reduce the working set.
如果你选择采用 coalesce 选项，就像在简单答案中所述，你仍然可以添加 where name is null or surname is null 子句来减少工作集。
Side note: I see you use $ like it was used in string interpolation. Always prefer to use prepared statements to avoid attacks via sql injection.
附注：我看到你使用 $ 就像它在字符串插值中使用一样。始终首选使用预处理语句以避免 SQL 注入攻击。
英文:
Looks like you could start a transaction with 2 statemnts and add a where clause that ignores correct rows:
BEGIN;
UPDATE public.people
SET flag = $flag,
name = $name
WHERE id_dt=$id_dt
AND name IS NULL;
UPDATE public.people
SET flag = $flag,
surname = $surname
WHERE id_dt=$id_dt
AND surname IS NULL;
COMMIT;
If you go with the coalesce option like stated in a simpling answer, you can still add a where name is null or surname is null clause to reduce the working set.
Side note: I see you use $ like it was used in string interpolation. Always prefer to use prepared statements to avoid attacks via sql injection.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论