How to update a table that is built from an inner join of two other tables-CodePudding

I've created a table (PostgresSQL), "rentals_detail", based on two other tables (customer & payment) as follows:

SELECT customer.customer_id, customer.first_name, customer.last_name, payment_id, payment.amount
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id;

The rentals_detail table comes out great. But I want to subsequently UPDATE this table whenever either the customer or payment tables get updated, and this is where I'm lost in the woods. Here is what my update logic looks like right now:

SET
customer_id = customer.customer_id,
first_name = customer.first_name,
last_name = customer.last_name,
payment_id = payment.payment_id,
amount = payment.amount
FROM payment, customer
WHERE payment.customer_id = customer.customer_id;

The problem with the above logic is that it updates the rentals_detail, in its entirety, with only the very first row from the customer table. That is, the first customer is simply copied throughout all rows of the rentals_detail table.

If I just want any and all updates to either the customer or payment tables to be applied to the rentals_detail table, how do I accomplish this?

CodePudding user response：

According to Postgres document, You can make query like below:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Sample query:

UPDATE 
  customer u_c
SET
  customer_id = c.customer_id,
  first_name = c.first_name,
  last_name = c.last_name,
  payment_id = p.payment_id,
  amount = p.amount
FROM 
  payment p
  INNER JOIN customer c ON p.customer_id = c.customer_id
WHERE
  c.id = u_c.id