UPDATE FROM 子句中的 GROUP BY

发布于 2024-10-21 14:03:48 字数 220 浏览 1 评论 0原文

我真的需要做这样的事情：

UPDATE table t1 
SET column1=t2.column1 
FROM table t2 
INNER JOIN table t3 
USING (column2) 
GROUP BY t1.column2;

但是 postgres 说我有关于 GROUP BY 子句的语法错误。有什么不同的方法可以做到这一点？

原文

I really need do something like that:

UPDATE table t1 
SET column1=t2.column1 
FROM table t2 
INNER JOIN table t3 
USING (column2) 
GROUP BY t1.column2;

But postgres is saying that I have syntax error about GROUP BY clause. What is a different way to do this?

分享到QQ

分享到微博

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

发布评论

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

挽梦忆笙歌 2024-10-28 14:03:48

UPDATE 语句不支持 GROUP BY，请参阅文档。如果您尝试使用 t2 中的相应行更新 t1，则需要使用 WHERE 子句，如下所示：

UPDATE table t1 SET column1=t2.column1
FROM   table t2
JOIN   table t3 USING (column2)
WHERE  t1.column2=t2.column2;

如果您需要在分配给 t1 之前对 t2/t3 中的行进行分组，则需要使用像这样的子查询：

UPDATE table t1 SET column1=sq.column1
FROM  (
   SELECT t2.column1, column2
   FROM   table t2
   JOIN   table t3 USING (column2)
   GROUP  BY column2
   ) AS sq
WHERE  t1.column2=sq.column2;

尽管按照公式编写，但它不起作用，因为 t2.column1 未包含在 GROUP BY 语句中（它必须是聚合函数而不是简单的列引用）。

否则，你到底想在这里做什么？

The UPDATE statement does not support GROUP BY, see the documentation. If you're trying to update t1 with the corresponding row from t2, you'd want to use the WHERE clause something like this:

UPDATE table t1 SET column1=t2.column1
FROM   table t2
JOIN   table t3 USING (column2)
WHERE  t1.column2=t2.column2;

If you need to group the rows from t2/t3 before assigning to t1, you'd need to use a subquery something like this:

UPDATE table t1 SET column1=sq.column1
FROM  (
   SELECT t2.column1, column2
   FROM   table t2
   JOIN   table t3 USING (column2)
   GROUP  BY column2
   ) AS sq
WHERE  t1.column2=sq.column2;

Although as formulated that won't work because t2.column1 isn't included in the GROUP BY statement (it would have to be an aggregate function rather than a simple column reference).

Otherwise, what exactly are you trying to do here?

回复收藏 0 原文

余生一个溪 2024-10-28 14:03:48

在 MariaDB/ MySQL 中，此 SQL 工作：

 UPDATE table t1 left join (
   SELECT t2.column1, column2
   FROM   table t2
   JOIN   table t3 USING (column2)
   GROUP  BY column2
   ) AS sq on t1.column2=sq.column2 
SET column1=sq.column1;

In MariaDB/ MySQL this SQL work :

 UPDATE table t1 left join (
   SELECT t2.column1, column2
   FROM   table t2
   JOIN   table t3 USING (column2)
   GROUP  BY column2
   ) AS sq on t1.column2=sq.column2 
SET column1=sq.column1;

回复收藏 0 原文

~没有更多了~