Update a table and display updated rows with a single SQL command

Question

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');

This didn't work!

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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

Single SQL statement

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.

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.

Single transaction

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 13, 2013 at 20:13

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2233540 Over a year ago

Hi! Thanks for your tip! That works very good. Do you know if there is another way to do the same without using RETURNING. I mean, just using SELECT, UPDATE SET and other basics clauses. My teacher has said we must use only clauses we've learned until now and RETURNING is not included.

Collectives™ on Stack Overflow

Update a table and display updated rows with a single SQL command

1 Answer 1

Single SQL statement

Single transaction

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Single SQL statement

Single transaction

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related