使用单个SQL命令更新表并显示更新的行 [英] Update a table and display updated rows with a single SQL command

问题描述

我需要您的PostgreSQL帮助.我有一个家庭作业，其中我必须更新表的列并显示所有已更新的信息，但是我必须使用单个命令来完成所有这些操作.更糟糕的是，我只能使用SELECT，UPDATE，SET，WHERE等基本子句.

I need your help with PostgreSQL. I have a homework in which I have to update a column of a table and display all the information that has been updated, but I have to use a single command to do all that. Even worse, I just can use basic clauses like SELECT, UPDATE, SET, WHERE etc.

这可能吗?我没有找到任何例子.

Is this possible? I didn't find any example.

我尝试了几种组合，例如:

I've tried several combinations like:

SELECT * FROM customer 
(UPDATE custumer SET bithdate = bithdate + INTERVAL '1 DAY'
 WHERE   bithcity = 'New York');

这没用！

推荐答案

由于这是家庭作业，因此我会为您保留一些东西.

Since this is homework, I'll leave something for you.

开始阅读有关> RETURNING UPDATE命令中的子句.

Start reading about the RETURNING clause of the UPDATE command in the manual.

(针对更复杂的情况)另一种替代方法是

Another alternative (for more complex scenarios) would be a data-modifying CTE. You still need the RETURNING clause for that.

或者您可以创建函数，您可以调用在单个语句中，但这违反了仅使用简单DML命令的要求.

Or you could create a function you can call in a single statement, but that would violate your requirement of only using simple DML commands.

如果允许使用多个语句，则另一个选择是将简单的UPDATE加上简单的SELECT包裹在

If more than a single statement is allowed, another option would be a simple UPDATE plus simple SELECT wrapped into a transaction.

这篇关于使用单个SQL命令更新表并显示更新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！