สถานการณ์
เมื่อเราต้องการอัปเดตข้อมูลในตารางหลัก (เช่น orders) โดยใช้ข้อมูล "ล่าสุด" จากตารางที่เก็บประวัติ (เช่น order_history) ซึ่งมีข้อมูลได้หลายรายการต่อหนึ่งออร์เดอร์
วิธีแก้ปัญหา
เราสามารถใช้ Common Table Expression (CTE) หรือ WITH clause เพื่อเตรียมชุดข้อมูลล่าสุดที่ต้องการก่อน แล้วจึงนำผลลัพธ์นั้นไปใช้ในคำสั่ง UPDATE ซึ่งทำให้โค้ดอ่านและจัดการได้ง่าย
ตัวอย่างโค้ด
สมมติว่าเรามีตาราง orders และ order_history เราต้องการอัปเดตสถานะล่าสุดของการสั่งซื้อมาไว้ที่ตาราง orders
หลักการทำงาน
1.
WITH latest_history AS (...): สร้างตารางเสมือนชื่อ latest_history ขึ้นมาเพื่อเก็บข้อมูลประวัติล่าสุดของแต่ละ order_id โดยอาศัยเทคนิค DISTINCT ON คู่กับ ORDER BY เพื่อกรองเอารายการที่ใหม่ที่สุด (created_at DESC)2.
UPDATE orders ... FROM latest_history: เป็น синтаксиสเฉพาะของ PostgreSQL ที่อนุญาตให้ดึงข้อมูลจากตารางอื่น (ในที่นี้คือ CTE ของเรา) มาใช้ในการอัปเดตได้โดยตรง3.
WHERE orders.id = latest_history.order_id: ทำหน้าที่เชื่อมข้อมูลระหว่างตารางเป้าหมายกับแหล่งข้อมูล เพื่อให้แน่ใจว่าอัปเดตถูกแถว
เคล็ดลับ: ก่อนรัน
UPDATE ควรทดสอบด้วย SELECT เพื่อดูตัวอย่างข้อมูลก่อนเสมอ เพื่อป้องกันความผิดพลาด