SQL Update/Delete Constraints

Explain how you would delete or update rows in Salesforce using only SELECT and INSERT operations.

Provide a PostgreSQL statement that reads the Salesforce contact snapshot and inserts a change plan representing required updates or deletions. Do not directly modify the snapshot table.

Output

1. One row per eligible contact, with plan_id, contact_id, operation, replacement_status, and replacement_phone.
2. Include inactive contacts, contacts with missing phone numbers, and deleted contacts. Sort by contact_id ascending.

Output details

• Treat a contact as eligible when is_deleted is true, status is Inactive, or phone is NULL.
• Set operation to DELETE for deleted contacts and UPDATE otherwise.
• Set deleted contacts' replacement_status and replacement_phone to NULL.
• Set inactive contacts' replacement_status to Archived; otherwise preserve the existing status.
• Set missing nondeleted phones to UNKNOWN; otherwise preserve the existing phone.
• Assign plan_id from the existing maximum, defaulting to 0, plus the contact's ascending sequential position, and insert into salesforce_contact_change_plan.