英文:
how to not update all rows and only restrict to the where clause
问题
我有一个在PostgreSQL中的更新查询，如下所示：
update table1 e
set (col1, col2) =
(select col1, col2 from table2 se
where e.id = se.id and se.col2 is not null);
然而，这会更新所有行，即使它们的 se.col2 为null。这些行会被更新为null。我只想更新那些 se.col2 不为null的行，而不是所有行。
英文:
I have an update query in postgres as such:
update table1 e
set (col1,col2) =
(select col1,col2 from table2 se
where e.id = se.id and se.col2 is not null);
However this updates all rows even the ones that are nulls for se.col2. The rows are updated as nulls. I want to just update where se.col2 is not null . Not all rows.
答案1
得分: 3
这很简单，如果你使用（非标准的）UPDATE语句的FROM子句：
UPDATE table1 e
SET (col1, col2) = (se.col1, se.col2)
FROM table2 se
WHERE e.id = se.id AND se.col2 IS NOT NULL;
这假设table2.id是唯一的，或者至少table2中的行不会多次匹配给定的table1行。
英文:
This is simple if you use the (non-standard) FROM clause of UPDATE:
UPDATE table1 e
SET (col1, col2) = (se.col1, se.col2)
FROM table2 se
WHERE e.id = se.id AND se.col2 IS NOT NULL;
This assumes that table2.id is unique, or at least that no more than one row of table2 matches any given table1 row.
答案2
得分: 1
update table1 e
set (col1, col2) = (
select col1, col2 from table2 se
where e.id = se.id
)
where exists (
select id from table2 se2
where se2.id = e.id
and col2 is not null
);
英文:
update table1 e
set (col1,col2) = (
select col1,col2 from table2 se
where e.id = se.id
)
where exists (
select id from table2 se2
where se2.id = e.id
and col2 is not null
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论