SQL Update table from another

Question

I have table as following:

dev=> \d statemachine_history
                                     Table "public.statemachine_history"
    Column     |           Type           |                             Modifiers                             
---------------+--------------------------+-------------------------------------------------------------------
 id            | bigint                   | not null default nextval('statemachine_history_id_seq'::regclass)
 schema_name   | character varying        | not null
 event         | character varying        | not null
 identifier    | integer                  | not null
 initial_state | character varying        | not null
 final_state   | character varying        | not null
 triggered_at  | timestamp with time zone | not null default statement_timestamp()
 triggered_by  | text                     | 
 command       | json                     | 
 flag          | json                     | 
 created_at    | timestamp with time zone | 
 created_by    | json                     | 
 updated_at    | timestamp with time zone | 
 updated_by    | json                     | 
Indexes:
    "statemachine_log_pkey" PRIMARY KEY, btree (id)
    "unique_statemachine_log_id" UNIQUE, btree (id)
    "statemachine_history_identifier_idx" btree (identifier)
    "statemachine_history_schema_name_idx" btree (schema_name)

AND

dev=> \d booking             
                                      Table "public.booking"
     Column     |           Type           |                      Modifiers                       
----------------+--------------------------+------------------------------------------------------
 id             | bigint                   | not null default nextval('booking_id_seq'::regclass)
 pin            | character varying        | 
 occurred_at    | timestamp with time zone | 
 membership_id  | bigint                   | 
 appointment_id | bigint                   | 
 created_at     | timestamp with time zone | 
 created_by     | json                     | 
 updated_at     | timestamp with time zone | 
 updated_by     | json                     | 
 customer_id    | bigint                   | 
 state          | character varying        | not null default 'booked'::character varying
Indexes:
    "booking_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "booking_appointment_id_fkey" FOREIGN KEY (appointment_id) REFERENCES appointment(id)
    "booking_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(id)
    "booking_membership_id_fkey" FOREIGN KEY (membership_id) REFERENCES membership(id)
Referenced by:
    TABLE "booking_decline_reason" CONSTRAINT "booking_decline_reason_booking_id_fkey" FOREIGN KEY (booking_id) REFERENCES booking(id)

I am trying to update the booking.update_at from the statemachine_history.updated_at

Letting you know that there is a one to many relationship between the 2 tables so i want to MAX(statemachine_history.updated_at)

My try is:

UPDATE booking SET updated_at=
                    (
                        SELECT MAX(updated_at)
                        FROM statemachine_history
                        WHERE schema_name='Booking'
                        AND identifier=id
                        GROUP BY identifier
                    );

However the bookings.updated_at becomes null

`AND identifier=id` will probably pick both `identifier` and `id` from booking, not `id` from statemachine_history. — Joachim Isaksson, Mar 14 '16 at 17:48
@JoachimIsaksson, sorry tag added by mistake, modified it :) — Dahab, Mar 14 '16 at 17:52
@JoachimIsaksson thanks so much, in addition i added this part: WHERE exists(SELECT id FROM statemachine_history WHERE schema_name='Booking' AND identifier=booking.id); check my solution — Dahab, Mar 14 '16 at 18:19

score 1 · Answer 1 · answered Mar 14 '16 at 17:55

All you really need to do is to make sure id reference booking.id by naming it explicitly;

UPDATE booking SET updated_at=
                (
                    SELECT MAX(updated_at)
                    FROM statemachine_history
                    WHERE schema_name='Booking'
                    AND identifier = booking.id
                    GROUP BY identifier
                );

A quick SQLfiddle to test with.

If there are real time requirements for the query, you'll want to look into TomH's join in another answer though.

score 0 · Answer 2 · answered Mar 14 '16 at 17:55

This should do what you need:

UPDATE B
SET
    updated_at = SQ.max_updated_at
FROM
    Booking B
INNER JOIN
(
    SELECT
        identifier,
        MAX(updated_at) AS max_updated_at
    FROM
        Statemachine_History SH
    GROUP BY
        identifier
) AS SQ ON SQ.identifier = B.id

score 0 · Answer 3 · answered Mar 14 '16 at 18:19

[Solved] PSQL Query:

UPDATE booking SET updated_at=
(
  SELECT MAX(updated_at)
  FROM statemachine_history
  WHERE schema_name='Booking'
        AND identifier=booking.id
  GROUP BY identifier
) WHERE exists(SELECT id FROM statemachine_history WHERE schema_name='Booking' AND identifier=booking.id);

This part:

WHERE exists(SELECT id FROM statemachine_history WHERE schema_name='Booking' AND identifier=booking.id);

is to avoid updating booking.updated_at, in case there is not such relation in statemachine_history table

SQL Update table from another

3 Answers3