ปัญหาการอัปเดตตารางด้วยข้อมูลจากตารางที่กำลังอัปเดต
You can't specify target table 'Q' for update in FROM clauseดูตัวอย่างโค้ดที่ก่อให้เกิดข้อผิดพลาด
UPDATE `table_name` AS Q SET `column1` = (
SELECT `column2` FROM `table_name` AS R WHERE R.`id` = Q.`id`
) WHERE Q.`id`=1ลองดูลำดับการทำงานของ Query ด้านบนดู
1. UPDATE `table_name` จะเป็นการเลือกตาราง
2. อ่านข้อมูลทีละแถว จนกว่าจะตรงตามเงื่อนไข WHERE Q.`id`=1
3. เมื่อได้แถวที่ตรงตามเงื่อนไข WHERE แล้ว จะ Query ข้อมูลตาม Query ย่อย (ไม่อธิบายนะครับ)
4. ส่งข้อมูลใน Query ย่อยไปอัปเดตใน Query หลัก
5. กลับไปทำข้อ 2.กับข้อมูลแถวถัดไป
จากลำดับการทำงานจะเห็นว่ามันเป็นการกระทำกับข้อมูลทีละแถวไปจนจบ ทีนี้ลองนึกภาพดูว่า หากเงื่อนไขการอัปเดตมันไปทำให้ข้อมูลในตารางเปลี่ยนแปลงจนไปเข้าเงื่อนไขล่ะ อะไรจะเกิดขึ้น
คำตอบก็คือ มันก็จะเกิดการวนอัปเดตไปอย่างไม่รู้จบ (จริงๆ มันก็คงจะไปจบที่ Server พังนั่นแหละ) SQL ถึงไม่อนุญาติให้ Update ตารางที่กำลัง Query อยู่
แล้วทีนี้ถ้าจำเป็นล่ะจะทำยังไง
จริงๆทางออกที่ถูกต้องที่สุดคือให้สำเนาข้อมูลต้นฉบับไปเป็นตารางใหม่ก่อน แล้วจึงทำการอัปเดตตารางต้นฉบับด้วยข้อมูลจากตารางที่สำเนาไว้ อาจดูยุ่งยากนิดหน่อยแต่ก็ปลอดภัยที่สุด
บทความนี้จะเสนออีกวิธีให้เลือกใช้
โดยหลักการแล้วคือมันไม่ยอมให้อัปเดตตารางด้วยข้อมูลจากตารางตัวเอง เราก็เลี่ยงไปให้ SQL สร้างสำเนาตารางใหม่ในหน่วยความจำแทน ลองดูคำสั่งด้านล่าง
UPDATE `table_name` AS Q
INNER JOIN `table_name` AS R ON R.`id` = Q.`id`
SET Q.`column1` = R.`column2`
WHERE Q.`id` = 1คำสั่ง JOIN จะทำให้ SQL ทำการจับคู่ข้อมูลให้เรียบร้อย แล้วนำข้อมูลที่จับคู่ได้ไปพักไว้ในตารางชั่วคราวก่อน จากนั้นจึงนำข้อมูลที่ได้ในตารางชั่วคราวที่สร้างขึ้น ไปอัปเดตตารางหลักอีกที จบ.....