การ update และ delete

สองคำกริยา CRUD สุดท้ายเปลี่ยนแปลงข้อมูลที่มีอยู่ UPDATE ตั้งค่า column ใหม่บน row ที่ตรงกับเงื่อนไข และ DELETE ลบ row ที่ตรงกัน ทั้งคู่พึ่งพา WHERE clause ที่คุณเรียนมาสำหรับ SELECT อย่างมาก — และทั้งคู่กลายเป็นความผิดพลาดที่สร้างความเสียหายหากคุณลืมใส่

เราใช้ table authors และ books กันต่อไป

การ update column

UPDATE ระบุชื่อ table แสดงรายการคู่ SET column = value และใช้ WHERE เพื่อเลือก row หากไม่มี WHERE ทุก row จะถูกเปลี่ยน

psql

UPDATE books
SET copies_sold = copies_sold + 500
WHERE id = 1;

Node.js

await pool.query(
  'UPDATE books SET copies_sold = copies_sold + $1 WHERE id = $2',
  [500, 1],
);

Python

cur.execute(
    "UPDATE books SET copies_sold = copies_sold + %s WHERE id = %s",
    (500, 1),
)

Go

_, err := conn.Exec(ctx,
    "UPDATE books SET copies_sold = copies_sold + $1 WHERE id = $2",
    500, 1)

Rust

sqlx::query("UPDATE books SET copies_sold = copies_sold + $1 WHERE id = $2")
    .bind(500_i32)
    .bind(1_i64)
    .execute(&pool)
    .await?;

ค่าใหม่สามารถอ้างถึงค่าเก่าได้ ดังที่ copies_sold + 500 แสดงให้เห็น คุณสามารถตั้งค่าหลาย column พร้อมกันได้โดยคั่นการกำหนดค่าแต่ละอันด้วยเครื่องหมายจุลภาค

ใน pgAdmin: รันสิ่งนี้ใน Query Tool แผงข้อความจะรายงานว่า UPDATE 1 ที่เป็นจำนวน row ที่ถูกเปลี่ยน

การยืนยันการเปลี่ยนแปลงด้วย RETURNING

เช่นเดียวกับ INSERT ทั้ง UPDATE และ DELETE รองรับ RETURNING นี่เป็นวิธีที่สะอาดที่สุดในการดูว่า row ใดถูกเปลี่ยนแปลงเป๊ะ ๆ และค่าใหม่เป็นอะไร

psql

UPDATE books
SET published = 2005
WHERE title = 'The Glass Orchard'
RETURNING id, title, published;

Node.js

const res = await pool.query(
  'UPDATE books SET published = $1 WHERE title = $2 RETURNING id, title, published',
  [2005, 'The Glass Orchard'],
);
console.log(res.rows);

Python

cur.execute(
    "UPDATE books SET published = %s WHERE title = %s RETURNING id, title, published",
    (2005, "The Glass Orchard"),
)
changed = cur.fetchall()

Go

rows, err := conn.Query(ctx,
    "UPDATE books SET published = $1 WHERE title = $2 RETURNING id, title, published",
    2005, "The Glass Orchard")

Rust

let rows = sqlx::query(
    "UPDATE books SET published = $1 WHERE title = $2 RETURNING id, title, published",
)
.bind(2005_i32)
.bind("The Glass Orchard")
.fetch_all(&pool)
.await?;

 id |       title       | published
----+-------------------+-----------
  1 | The Glass Orchard |      2005
(1 row)

การลบ row

DELETE FROM ลบ row ที่ตรงกับ WHERE clause เช่นเดียวกับ UPDATE คุณสามารถเพิ่ม RETURNING เพื่อดูว่าอะไรถูกลบไป

psql

DELETE FROM books
WHERE copies_sold = 0
RETURNING id, title;

Node.js

const res = await pool.query(
  'DELETE FROM books WHERE copies_sold = $1 RETURNING id, title',
  [0],
);

Python

cur.execute(
    "DELETE FROM books WHERE copies_sold = %s RETURNING id, title",
    (0,),
)
removed = cur.fetchall()

Go

rows, err := conn.Query(ctx,
    "DELETE FROM books WHERE copies_sold = $1 RETURNING id, title",
    0)

Rust

let rows = sqlx::query("DELETE FROM books WHERE copies_sold = $1 RETURNING id, title")
    .bind(0_i32)
    .fetch_all(&pool)
    .await?;

 id |     title
----+----------------
  3 | Quiet Harbors
(1 row)

อันตรายของ WHERE ที่หายไป

UPDATE และ DELETE ทำกับทุก row ที่ตรงกับเงื่อนไข — และถ้าไม่มีเงื่อนไข ทุก row ก็ตรงทั้งหมด การรัน DELETE FROM books; จะล้าง table ทั้งหมดให้ว่างเปล่า และ UPDATE books SET copies_sold = 0; จะทำให้ทุกระเบียนเป็นศูนย์ ไม่มีปุ่ม undo

นิสัยที่ปลอดภัยคือการรัน WHERE เดียวกันเป็น SELECT ก่อน ยืนยันจำนวน row แล้วค่อยสลับคำกริยา การห่อการเปลี่ยนแปลงที่เสี่ยงไว้ใน transaction จะให้ทางหนีฉุกเฉินแก่คุณ

psql

BEGIN;
DELETE FROM books WHERE author_id = 4;
-- inspect the result, then decide:
ROLLBACK;  -- or COMMIT; to keep the change

Node.js

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('DELETE FROM books WHERE author_id = $1', [4]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

Python

with conn.transaction():
    cur.execute("DELETE FROM books WHERE author_id = %s", (4,))
# commits if the block succeeds, rolls back on exception

Go

tx, err := conn.Begin(ctx)
if err != nil {
    return err
}
if _, err := tx.Exec(ctx, "DELETE FROM books WHERE author_id = $1", 4); err != nil {
    tx.Rollback(ctx)
    return err
}
err = tx.Commit(ctx)

Rust

let mut tx = pool.begin().await?;
sqlx::query("DELETE FROM books WHERE author_id = $1")
    .bind(4_i64)
    .execute(&mut *tx)
    .await?;
tx.commit().await?;

ภายใน transaction ไม่มีอะไรถาวรจนกว่าคุณจะ COMMIT ส่วน ROLLBACK จะทิ้งการเปลี่ยนแปลงไป transaction มีบทเรียนเต็ม ๆ ของตัวเองอยู่ช่วงหลังของคอร์ส

เคล็ดลับและข้อควรระวัง

• ใส่ WHERE clause บน UPDATE และ DELETE เสมอ เว้นแต่คุณตั้งใจจริง ๆ ที่จะแตะต้องทุก row การลืม WHERE จะเขียนทับหรือล้าง table ทั้งหมดให้ว่างเปล่า
• แสดงตัวอย่างคำสั่งที่สร้างความเสียหายล่วงหน้าโดยการรัน SELECT count(*) ... WHERE ... ที่ตรงกันก่อน เพื่อยืนยันว่าคุณกำลังจะกระทบกี่ row
• ห่อการเปลี่ยนแปลงที่เสี่ยงหรือหลายขั้นตอนไว้ใน transaction เพื่อให้คุณ ROLLBACK ได้ถ้าผลลัพธ์ผิด
• ใช้ RETURNING เพื่อตรวจสอบว่า row ใดถูกเปลี่ยนแปลงเป๊ะ ๆ แทนการไว้ใจแค่จำนวน row เพียงอย่างเดียว
• DELETE อาจล้มเหลวได้ถ้า table อื่นอ้างถึง row นั้นผ่าน foreign key คุณจะกลับมาเจอเรื่องนี้อีกในโมดูล join และความสัมพันธ์

🐘 WHERE ไม่ใช่ตัวเลือกในทางปฏิบัติ

อุบัติเหตุบน production ที่พบบ่อยที่สุดอันดับหนึ่งกับ SQL คือ UPDATE หรือ DELETE ที่รันโดยไม่มี WHERE clause สร้างความเคยชินให้กล้ามเนื้อจดจำ: พิมพ์ WHERE ก่อน แล้วค่อยพิมพ์ค่าทีหลัง เมื่อสงสัย ให้ทำใน transaction ที่ rollback ได้

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
UPDATE/DELETE พร้อม WHERE ที่เจาะจง	ปลอดภัย กระทบเฉพาะ row ที่ตั้งใจ	ต้องคิดเงื่อนไขให้ถูกต้องครบถ้วนก่อนรัน
UPDATE/DELETE โดยไม่มี WHERE	เขียนเร็ว ไม่ต้องคิดเงื่อนไข	กระทบทุก row ในทันที เป็นหายนะถ้ารันผิด table หรือผิด environment
ห่อไว้ใน transaction แล้ว ROLLBACK ได้	มีทางหนีฉุกเฉินก่อน COMMIT จริง	ต้องจำเปิด/ปิด transaction และตรวจผลก่อน commit ทุกครั้ง
รันตรง ๆ นอก transaction	เร็ว ไม่ต้องจัดการ transaction	ไม่มีปุ่ม undo ถ้าผลลัพธ์ผิดพลาด

ข้อผิดพลาดที่พบบ่อย

• รัน UPDATE/DELETE โดยไม่มี WHERE clause — กระทบทุก row ใน table ทันที ให้สร้างนิสัยพิมพ์ WHERE ก่อนแล้วค่อยเติมเงื่อนไข
• ไม่ทดสอบเงื่อนไขด้วย SELECT ก่อน — ควรรัน SELECT count(*) ... WHERE ... ที่ตรงกันก่อนเสมอ เพื่อยืนยันจำนวน row ที่จะโดนผลกระทบ ก่อนสลับเป็น UPDATE/DELETE
• คิดว่า DELETE คืนพื้นที่ดิสก์ทันที — DELETE แค่ทำเครื่องหมาย row ว่าตายแล้ว การคืนพื้นที่จริงเป็นหน้าที่ของ VACUUM

💡 ตัวอย่างจากของจริง

กระบวนการเปลี่ยนแปลงข้อมูล production ของ GitLab — กำหนดให้ dry-run คำสั่ง SQL ที่ทำลายข้อมูล (destructive) ด้วยเวอร์ชัน SELECT ก่อนเสมอ แล้วให้ทีมรีวิวจำนวน row ก่อนอนุมัติให้รัน UPDATE/DELETE จริง

ทีม SRE ทั่วไปที่ทำ manual data fix — บังคับให้ทุกการเปลี่ยนแปลงข้อมูลด้วยมือห่อด้วย transaction ที่ ROLLBACK ได้ พร้อมมีคนที่สองช่วยรีวิวผลก่อน COMMIT

จะเกิดอะไรขึ้นถ้ารัน UPDATE books SET copies_sold = 0; โดยไม่มี WHERE clause?

อัปเดตแค่ row แรก ทุก row ใน table ถูกตั้งเป็น 0 PostgreSQL ปฏิเสธไม่ยอมรัน ยังไม่มีอะไรเปลี่ยนจนกว่าจะ commit

clause ตัวไหนทำให้ UPDATE หรือ DELETE รายงานได้ว่าเปลี่ยน row ไหนไปบ้าง?

SET RETURNING WHERE LIMIT

จะทำให้ DELETE ที่เสี่ยงสามารถย้อนกลับได้ระหว่างที่ยังตรวจผลอยู่ได้อย่างไร?

รันซ้ำสองครั้ง ห่อไว้ใน transaction แล้ว ROLLBACK ถ้าผิด เพิ่ม ORDER BY ใช้ DELETE ALL แทน

Submit

Mark as complete