使用不同的列更新多个记录

发布于 2025-02-13 09:35:28 字数 526 浏览 0 评论 0原文

我想知道我是否可以/如何使用单个查询中设置的不同列更新多个记录。

因此，我有一张表

Test {
   id integer
   name varchar
   age integer
}

，然后我有一个test1和测试2的

test1 = Test {id=1, name="Joe"}
test2 = Test {id=2, age=22}

表，即使每个都在更新其他列，也可以有一个查询来更新这两个？

如果我有多个具有相同字段的对象，类似于以下作用：

UPDATE test as t 
SET id = u.id, age = u.age, name = u.name 
FROM (VALUES (1, 21, 'Ronan'),(2, 65, 'Conor') ) 
AS u(id, age, name) 
WHERE u.id = t.id

有人可以为我清除它吗？我正在使用Postgres如果有所不同

原文

I want to know if I can/how to update multiple records with different columns set in a single query.

So I have a table

Test {
   id integer
   name varchar
   age integer
}

Then for test1 and test 2 i have

test1 = Test {id=1, name="Joe"}
test2 = Test {id=2, age=22}

Is it possible to have a single query to update both of these even though each is updating a different column?

If i have multiple objects that all have the same fields something like the following works:

UPDATE test as t 
SET id = u.id, age = u.age, name = u.name 
FROM (VALUES (1, 21, 'Ronan'),(2, 65, 'Conor') ) 
AS u(id, age, name) 
WHERE u.id = t.id

Anyone able to clear this up for me? Im using Postgres if that makes a difference

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小矜持 2025-02-20 09:35:28

更新总是设置列出的每一列，没有列出。因此，没有直接的方法在单个语句中更新不同的列。但是，您可以通过指定不想更新的列来指定null间接执行此操作值为null）。类似：（参见

with new_val (id, name, age) as 
     ( values (1, null, 40) 
            , (2,'Paul',null) 
     ) 
update test  t
   set name = coalesce( nv.name, t.name) 
     , age  = coalesce( nv.age, t.age) 
  from new_val nv
 where t.id = nv.id;

159E9021D786B89034B3C4B3C44444E4E9BD2755如果需要在几个列和/或行上，这可能会变得很混乱。对于特定列的特定更新似乎既容易又更安全。

An update always sets each column listed and none not listed. So there is no direct way of updating different columns in a single statement0. However you can do so indirectly by specifying null for the columns you do not want updated then using the coalesce function to set those columns to their existing values (it cannot however set a value to null). Something like: (see demo)

with new_val (id, name, age) as 
     ( values (1, null, 40) 
            , (2,'Paul',null) 
     ) 
update test  t
   set name = coalesce( nv.name, t.name) 
     , age  = coalesce( nv.age, t.age) 
  from new_val nv
 where t.id = nv.id;

The question becomes: Is it really worth it? This could become quite messy if needed on more than a couple columns and/or rows. Seems specific updates for specific columns is both easier and safer.

回复收藏 0 原文

~没有更多了~