SQL สำหรับกรณี … ถ้าไม่มีให้ Insert ถ้ามีให้ Update ถ้าเกินให้ Delete

บางครั้งเรามีข้อมูลอยู่ชุดหนึ่ง อยากจะ insert เข้าตาราง แต่จะสั่ง insert เลยเราก็ไม่แน่ใจว่าจะมี key ของบางเรคคอร์ดอยู่ในตารางปลายทางอยู่แล้วหรือเปล่า เพราะถ้ามีข้อมูลซ้ำกันแม้แต่เรคคอร์ดเดียวก็จะเกิด primary key violation และทำให้ insert ไม่สำเร็จ เราอยากให้มันดูเป็นรายบรรทัดได้ว่าปลายทางมี key นั้นๆ หรือยัง หากยังไม่มีจึงจะให้ insert หากมีอยู่แล้วก็ให้ update การดำเนินการลักษณะนี้เราเรียกว่าการ upsert (มาจากคำว่า update + insert) คือสามารถที่จะเลือก insert หรือ update ได้ตามแต่ว่าตารางปลายทางมี key นั้นอยู่แล้วหรือไม่

อีกสถานการณ์หนึ่งเช่นการซิงค์ข้อมูลระหว่างสองตาราง คือมีตารางต้นฉบับอยู่ แล้วก็มีตารางปลายทาง แล้วเราต้องการให้ insert บรรทัดที่ยังไม่มีในปลายทาง, update บรรทัดที่ปลายทางมีเหมือนต้นทาง, และ delete บรรทัดที่มีในปลายทางแต่ไม่มีในตารางต้นทางแล้ว

ทั้งสองสถานการณ์ข้างต้นนั้น ถ้าเอาแบบตรงไปตรงมา ก็คือ select มาเช็คทีละเรคคอร์ดดูว่ามี key นั้นอยู่แล้วหรือเปล่า แล้วเลือก insert, update, delete ตามแต่เงื่อนไข แต่การเขียนแบบนี้ นอกจากจะเมื่อยมือมากขึ้น (เพราะโค้ดยาวขึ้น) จำนวน query ที่วิ่งลงไปใน DB ก็จะมีจำนวนมากเป็นสัดส่วนตามจำนวนเรคคอร์ดของข้อมูลที่มีเลยทีเดียว หากข้อมูลมีเป็นพันเป็นหมื่นคงไม่ใช่เรื่องดี

ใน MS SQL Server (2008 ขึ้นไป) จะมีคำสั่งที่สามารถ insert, update, delete ทุกอย่างจบใน commit เดียวกันได้ นั่นคือคำสั่ง MERGE
ไม่ว่าข้อมูลจะมากน้อยขนาดไหน ทุกอย่างสั่งได้ด้วย query เดียว

รูปแบบโดยทั่วไปของคำสั่ง MERGE คือ

MERGE INTO DestTable dst
  USING SourceQuery src
  ON src.key1=dst.key1 AND src.key2=dst.key2
WHEN NOT MATCHED BY TARGET THEN 
  INSERT (field1, field2, field3)
  VALUES (field1, field2, field3)
WHEN MATCHED THEN
  UPDATE SET dst.field1=src.field1, dst.field2=src.field2, dst.field3=src.field3
WHEN NOT MATCHED BY SOURCE
  THEN DELETE;

โดยที่ DestTable คือตารางปลายทางที่ต้องการจะแก้ไข
dst คือชื่อ alias ของตารางปลายทาง อันนี้จริงๆ จะตั้งว่าอย่างไรก็ได้ แต่ตั้งว่า dst ก็จะทำให้เข้าใจง่ายดี
SourceQuery คือตารางหรือคิวรีของข้อมูลต้นทาง
src คือชื่อ alias ของตารางต้นทาง จะตั้งเป็นอย่างอื่นก็ได้เช่นกัน
key1, key2 คือ Unique Key ของข้อมูล ที่ใช้ในการแมตช์ข้อมูลต้นทางและปลายทาง ตัวอย่างนี้มี key สองฟิลด์ แต่จริงๆ จะมีกี่ฟิลด์ก็ได้
field1, field2, field3 คือรายการของฟิลด์ที่ต้องการจะ insert หรือ update ซึ่งรายการฟิลด์ที่จะ insert ไม่จำเป็นต้องเหมือนฟิลด์ที่จะ update ก็ได้
อย่าลืม semicolon ปิดท้าย

โดยสรุปคือในคำสั่ง merge เราต้องระบุไปว่า ต้องการแก้ไขตารางอะไร โดยใช้ข้อมูลจากไหน เอาอะไรเป็น key ในการเช็ค ว่าเออ key นี้ต้นทางมีนะแต่ปลายทางไม่มี (unmatched by target) ก็จะทำการ insert, ถ้าเช็คแล้วพบ key ทั้งสองด้าน (matched) ก็จะทำการ update, หรือสุดท้ายถ้าเช็คแล้วเป็น key ที่ปลายทางมีแต่ต้นทางไม่มี (unmatched by source) ก็จะทำการ delete

หากเราต้องการแค่ upsert คือไม่ต้องมีการ delete ก็สามารถตัดเงื่อนไข “when not matched by source” ไปจนจบ delete ออกได้ หรือถ้าไม่ต้องการ update ก็สามารถตัดส่วนของเงื่อนไข “when matched” หรือไม่ต้องการ insert ก็ตัด “when not matched by target” ได้เช่นกัน

ตัวอย่าง
สมมติเรามีตารางต้นฉบับชื่อ Employee หน้าตาแบบนี้
merge-pic1
ส่วนตารางปลายทางชื่อ EmpoyeeBkk หน้าตาแบบนี้
merge-pic2

โจทย์ของเราคือต้องการซิงค์ข้อมูลจาก Employee ไปลง EmployeeBkk โดยคัดเฉพาะพนักงานสาขา กทม. และเอาเฉพาะที่ยัง active อยู่ นั่นคือ query ที่เป็น source ของเราจะเป็นดังนี้

select * from Employee where branch='กทม' and active=1

คำสั่งในการ merge ของเราก็จะเป็นดังนี้

MERGE INTO EmployeeBkk dst
  USING (select * from Employee where branch='กทม' and active=1) src
  ON src.emp_id=dst.emp_id
WHEN NOT MATCHED BY TARGET THEN 
  INSERT (emp_id, name, degree)
  VALUES (emp_id, emp_name, degree)
WHEN MATCHED THEN
  UPDATE SET dst.name=src.emp_name, dst.degree=src.degree
WHEN NOT MATCHED BY SOURCE
  THEN DELETE;

ซึ่งทันทีที่รันคำสั่งข้างต้น ตารางปลายทางเราก็จะมีข้อมูลดังนี้
merge-pic3

และหากข้อมูลต้นทางมีการเปลี่ยนแปลง ไม่ว่าจะมีการเพิ่ม/ลดเรคคอร์ดหรือฟิลด์เปลี่ยนแปลง เมื่อเรารันคำสั่งนี้ซ้ำอีกครั้ง มันก็จะทำการปรับปรุงตารางปลายทางตามตารางต้นทางทุกประการ

คำสั่ง MERGE นี้มีอยู่ในมาตรฐาน SQL:2003 และฐานข้อมูล DBMS หลายตัวก็สนับสนุน แต่ในรายละเอียดแล้วอาจจะไม่เหมือนกันซะทีเดียว (ก็ไม่รู้ว่า implement standard กันอีท่าไหน) อย่างที่ผมโพสในบทความนี้ก็เป็นรูปแบบของ MS SQL เท่านั้นนะครับ ของยี่ห้ออื่นอาจะไม่ได้เขียนแบบนี้

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: