สถานการณ์

เมื่อเราต้องการอัปเดตข้อมูลในตารางหลัก (เช่น orders) โดยใช้ข้อมูล "ล่าสุด" จากตารางที่เก็บประวัติ (เช่น order_history) ซึ่งมีข้อมูลได้หลายรายการต่อหนึ่งออร์เดอร์

วิธีแก้ปัญหา

เราสามารถใช้ Common Table Expression (CTE) หรือ WITH clause เพื่อเตรียมชุดข้อมูลล่าสุดที่ต้องการก่อน แล้วจึงนำผลลัพธ์นั้นไปใช้ในคำสั่ง UPDATE ซึ่งทำให้โค้ดอ่านและจัดการได้ง่าย

ตัวอย่างโค้ด

สมมติว่าเรามีตาราง orders และ order_history เราต้องการอัปเดตสถานะล่าสุดของการสั่งซื้อมาไว้ที่ตาราง orders

-- 1. เตรียมข้อมูลล่าสุดด้วย CTE
WITH latest_history AS (
    SELECT DISTINCT ON (order_id) -- เลือกมาแค่ 1 แถวต่อ order_id
        order_id,
        status,
        next_action_date,
        created_at
    FROM
        order_history
    WHERE
        is_archived = FALSE
    ORDER BY
        order_id, created_at DESC -- จัดลำดับให้แถวแรกคือรายการล่าสุด
)
-- 2. นำข้อมูลจาก CTE มาอัปเดตตารางหลัก
UPDATE
    orders
SET
    latest_status = latest_history.status,
    follow_up_date = latest_history.next_action_date,
    last_contact_at = latest_history.created_at
FROM
    latest_history
WHERE
    orders.id = latest_history.order_id; -- เงื่อนไขการ Join ข้อมูล

หลักการทำงาน

1. WITH latest_history AS (...): สร้างตารางเสมือนชื่อ latest_history ขึ้นมาเพื่อเก็บข้อมูลประวัติล่าสุดของแต่ละ order_id โดยอาศัยเทคนิค DISTINCT ON คู่กับ ORDER BY เพื่อกรองเอารายการที่ใหม่ที่สุด (created_at DESC)

1. UPDATE orders ... FROM latest_history: เป็น синтаксиสเฉพาะของ PostgreSQL ที่อนุญาตให้ดึงข้อมูลจากตารางอื่น (ในที่นี้คือ CTE ของเรา) มาใช้ในการอัปเดตได้โดยตรง

1. WHERE orders.id = latest_history.order_id: ทำหน้าที่เชื่อมข้อมูลระหว่างตารางเป้าหมายกับแหล่งข้อมูล เพื่อให้แน่ใจว่าอัปเดตถูกแถว