Insert result to table of updating column

Question

I have this situation I'm doing a update statement on sql script in postgres, when I found matching record I want to update content and also save to other table for example id_user for who's I did update is it possible with use plpgsql. Can you give me any tips what terms I will should search on google ?

ex.

update User set Status "Active" where any_condition when success insert to UserHistoryTable

Jim Jones · Accepted Answer · 2021-05-27 13:25:08Z

1

Use a normal update with a RETURNING * inside a CTE, and from there insert the data in the history table:

WITH j AS (
 UPDATE tbuser SET status = 'Active' 
 WHERE id = 42 
 RETURNING *)
INSERT INTO user_hist SELECT * FROM j;

Demo: db<>fiddle

If you want to save the value before the update I suggest you take a look at triggers.

answered May 27, 2021 at 13:25

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

Insert result to table of updating column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related