Learning PostgreSQL 11 - Third Edition

Changing data in multiple tables at a time

Another very useful application of CTEs is performing several data-changing statements at once. This is done by including the INSERT, UPDATE, and DELETE statements in CTEs. The results of any of these statements can be passed to the following CTEs or to the primary query by specifying the RETURNING clause. As well as for SELECT statements, the maximum number of common table expressions that change data is not defined.

For example, suppose you want to add a new car to the car portal database and there is no corresponding car model in the car_model table. To do this, you need to enter a new record in the car_model table, take the ID of the new record, and use this ID to insert the data into the car ...