update foreign keys on bulk insert

A booking has many appointments.

I'm working on a db migration to create a booking for all existing appointments without a booking. I have this so far:

INSERT INTO bookings (status)
SELECT (status)
FROM appointments
WHERE appointments.booking_id IS NULL

But I’m stumped with how to update appointments.booking_id with the newly created bookings.

I'd prefer not to make a new query for each appointment, as there are many existing appointments and performance is a factor. (Normally I rely on the ORM, hence this basic SQL question)

Using Postgres

728x90

1 Answers update foreign keys on bulk insert

Create a trigger which will insert value to the table after values in booking table are inserted.

CREATE TRIGGER trigger_name 
ON table_name 
FOR INSERT 
AS 
  BEGIN 
      SET nocount ON 

      IF EXISTS (SELECT ( status ) 
                 FROM   appointments 
                 WHERE  appointments.booking_id IS NULL) 
        BEGIN 
            INSERT INTO table_name 
                        (column_name) 
            SELECT 'xxx' -- Replace this value with what you wanna insert  
            FROM   appointments 
            WHERE  appointments.booking_id IS NULL 
        END 
  END 

4 months ago