SQL UPDATE với JOIN: Cách hoạt động

Tính nhất quán của dữ liệu rất quan trọng đối với độ tin cậy và tính toàn vẹn trong cơ sở dữ liệu quan hệ. Khi sửa đổi dữ liệu hiện có trên các bảng liên quan, chúng ta sử dụng câu lệnh UPDATE cùng với mệnh đề JOIN để đạt được tính nhất quán dữ liệu. Thao tác UPDATE với JOIN được hỗ trợ trong SQL Server và hữu ích để đảm bảo đồng bộ dữ liệu chính xác giữa các bảng.

Thao tác JOIN trong SQL cho phép thực hiện các cập nhật phức tạp hơn ngoài phạm vi một bảng thông qua các khóa ngoại được dùng chung giữa các bảng khác nhau. Thao tác UPDATE với JOIN hữu ích cho nhà phát triển và quản trị cơ sở dữ liệu vì nó nâng cao hiệu quả và giảm rủi ro dữ liệu không nhất quán. 

Trước khi bắt đầu, tôi khuyến nghị bạn học khóa Introduction to SQL Server của DataCamp để làm quen với cách hoạt động của cơ sở dữ liệu SQL Server. Hướng dẫn này được viết riêng cho SQL Server. Ngoài ra, hãy xem hướng dẫn Introduction to SQL Joins của chúng tôi để học cách dùng JOIN khi làm việc với nhiều bảng trong SQL.

Cách sử dụng SQL UPDATE với JOIN

Trong SQL Server, thao tác UPDATE với JOIN cho phép bạn cập nhật bản ghi trong một bảng dựa trên dữ liệu khớp từ bảng khác. Kỹ thuật này đặc biệt hữu ích khi đồng bộ dữ liệu qua nhiều bảng.

Sử dụng SQL UPDATE với INNER JOIN

Trong SQL, INNER JOIN truy xuất các hàng/bản ghi có dữ liệu khớp ở cả hai bảng. Khi dùng với câu lệnh UPDATE, INNER JOIN đảm bảo chỉ những bản ghi trong bảng đích có bản ghi tương ứng trong bảng được join mới được cập nhật.

Cấu trúc điển hình của UPDATE với INNER JOIN gồm việc chỉ định bảng đích cần cập nhật và xác định các cột sẽ được cập nhật. Cấu trúc cũng bao gồm việc sử dụng INNER JOIN để liên kết với bảng khác dựa trên một cột chung nhằm chỉ cập nhật các bản ghi cần thiết.

Ví dụ INNER JOIN

Giả sử bạn quản lý một cơ sở dữ liệu bán lẻ nơi cần cập nhật bảng Sales để đồng bộ với thay đổi trong bảng Customer. Một khách hàng vừa thay đổi phương thức liên hệ ưa thích và bạn muốn những thay đổi này được phản ánh ở cả hai bảng. Bạn sẽ dùng UPDATE với INNER JOIN để đảm bảo chỉ các bản ghi bán hàng liên quan đến khách hàng đã cập nhật thông tin liên hệ mới được sửa đổi, không ảnh hưởng đến các hàng khác trong dữ liệu.

-- Update the Sales table based on the new preferred contact methods in the Customers table
UPDATE s
SET s.ContactMethod = c.PreferredContactMethod
FROM Sales s
INNER JOIN Customers c ON s.CustomerID = c.CustomerID
WHERE c.CustomerID IN (3, 7, 9);

Thực hành tốt với UPDATE và INNER JOIN

Khi dùng UPDATE với INNER JOIN, hãy cân nhắc các thực hành tốt sau để truy vấn hiệu quả.

• Dùng chỉ mục trên các cột join: Đảm bảo các cột dùng trong điều kiện JOIN được lập chỉ mục để tối ưu hiệu năng, tăng tốc độ thực thi truy vấn.

• Giới hạn phạm vi cập nhật: Sử dụng mệnh đề WHERE để chỉ cập nhật các hàng liên quan và cải thiện hiệu năng truy vấn.

Sử dụng SQL UPDATE với LEFT JOIN

LEFT JOIN được dùng để truy xuất tất cả bản ghi từ bảng bên trái (thứ nhất) và các bản ghi khớp từ bảng bên phải (thứ hai). Truy vấn với LEFT JOIN sẽ trả về NULL từ bảng bên phải nếu không có kết quả khớp. Khi dùng với câu lệnh UPDATE, LEFT JOIN có thể cập nhật bản ghi ngay cả khi không có dữ liệu khớp ở bảng thứ hai, điều này hữu ích để điền dữ liệu còn thiếu. Hãy xem SQL Joins Cheat Sheet nếu bạn muốn ôn lại về các kiểu join trong SQL.

Ví dụ LEFT JOIN

Xem xét kịch bản bạn quản lý một cơ sở dữ liệu cho cửa hàng bán lẻ trực tuyến. Bảng Customers chứa danh sách tất cả khách hàng, trong khi bảng Orders chứa chi tiết đơn hàng của một số khách hàng này. Bạn sẽ dùng LEFT JOIN để truy xuất và cập nhật chi tiết đơn hàng cho tất cả khách hàng, bao gồm cả những người chưa từng đặt hàng. Phương pháp này sẽ điền dữ liệu còn thiếu cho các khách hàng chưa có đơn bằng giá trị NULL hoặc giá trị được chỉ định.

Ví dụ với LEFT JOIN của chúng ta sẽ hơi khác một chút vì LEFT JOIN trả về các giá trị NULL nếu không tìm thấy bản ghi khớp ở bảng bên phải. Vì lý do này, điều quan trọng là tìm cách xử lý các giá trị NULL. Bạn có thể xử lý các giá trị NULL bằng cách đặt giá trị mặc định cho các cột cần cập nhật. Sử dụng hàm COALESCE(), LEFT JOIN sẽ bao gồm điều kiện cho giá trị mặc định với dữ liệu bị thiếu. Truy vấn sau minh họa cách đặt giá trị mặc định cho các giá trị NULL.

-- Update all customers to set default values where OrderStatus and OrderDate are NULL
UPDATE c
SET 
    c.OrderStatus = COALESCE(o.OrderStatus, 'No Orders'),
    c.OrderDate = COALESCE(o.OrderDate, '2023-01-01')   
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
-- Select from the updated Customers table
SELECT * 
FROM Customers;

Bạn cũng có thể dùng hàm COALESCE() hoặc IFNULL() để xử lý các giá trị NULL liên quan đến phép tính trước khi cập nhật cột. Hãy xem hướng dẫn COALESCE() SQL Function của chúng tôi để tìm hiểu thêm.

Thực hành tốt với UPDATE và LEFT JOIN

Khi dùng UPDATE với LEFT JOIN, hãy cân nhắc các thực hành tốt sau để truy vấn hiệu quả.

• Tham chiếu cột rõ ràng: Thay vì dùng SELECT *, hãy chỉ rõ các cột bạn muốn cập nhật để truy vấn dễ hiểu hơn và tránh cập nhật ngoài ý muốn.

• Dùng chỉ mục trên các cột join: Đảm bảo các cột được dùng trong điều kiện JOIN có chỉ mục để cải thiện hiệu năng thao tác JOIN và thời gian thực thi truy vấn tổng thể.

Cân nhắc hiệu năng khi UPDATE với JOIN trong SQL

Việc hiểu các tác động về hiệu năng khi dùng UPDATE với JOIN trong SQL rất quan trọng để đảm bảo truy vấn chạy hiệu quả. Hiệu năng của các thao tác này có thể bị ảnh hưởng bởi nhiều yếu tố, bao gồm kích thước các bảng được dùng, loại join sử dụng và việc có hay không các chỉ mục phù hợp.

Chỉ mục và tốc độ truy vấn

Lập chỉ mục cho các cột tham gia điều kiện join rất quan trọng để tăng tốc quy trình cập nhật. Khi bạn lập chỉ mục phù hợp cho các cột, thao tác chỉ cần định vị các hàng khớp trong lúc join, giảm thời gian quét bảng.

Tác động của bộ dữ liệu lớn

Khi làm việc với bộ dữ liệu lớn, việc thực thi truy vấn có thể chậm hơn do các thao tác tiêu tốn tài nguyên. Hãy cân nhắc các thực hành tốt sau để giảm thiểu vấn đề hiệu năng với dữ liệu lớn. Ngoài ra, hãy thử lộ trình kỹ năng SQL Server for Database Administrators nếu bạn thường xuyên thiết kế hoặc vận hành cơ sở dữ liệu trong công việc. 

• Cập nhật theo lô: Chia nhỏ các lần cập nhật thành các lô để giảm tải cho cơ sở dữ liệu và cập nhật bản ghi hiệu quả hơn.

• Giới hạn phạm vi cập nhật: Khi bạn dùng mệnh đề WHERE, bạn xác định các hàng cần cập nhật, từ đó giảm thời gian xử lý truy vấn.

Lỗi thường gặp và cách tránh

Có một số lỗi thường gặp khi dùng UPDATE với JOIN trong SQL Server. Những lỗi này có thể dẫn đến vấn đề hiệu năng hoặc cập nhật dữ liệu ngoài ý muốn. Dưới đây là một số lỗi và cách tránh.

Cập nhật ngoài ý muốn

Nếu bạn không chỉ định đúng điều kiện join, thao tác UPDATE với JOIN sẽ cập nhật nhiều bản ghi hơn dự định. Lỗi này có thể cập nhật toàn bộ bảng thay vì tập con cần thiết. Để tránh lỗi này, luôn đảm bảo bạn dùng điều kiện JOIN chính xác để cập nhật đúng các hàng cần thiết.

Tích Descartes (Cartesian product)

Tích Descartes xảy ra khi điều kiện join sai hoặc thiếu, khiến truy vấn khớp mọi hàng từ bảng bên trái với mọi hàng từ bảng bên phải. Vấn đề này có thể làm chậm thao tác truy vấn và cho ra kết quả không chính xác. Để ngăn lỗi này, luôn đảm bảo bạn định nghĩa đúng điều kiện JOIN. Đồng thời, hãy chỉ rõ các cột đúng để áp dụng điều kiện join.

Các lựa chọn thay thế cho SQL UPDATE với JOIN

Mặc dù UPDATE với JOIN là phương pháp mạnh mẽ để cập nhật bản ghi giữa các bảng liên quan, vẫn có những cách khác để đạt được cập nhật tương tự. Dưới đây là một số lựa chọn.

Truy vấn con (subquery)

Subquery là một lựa chọn quan trọng thay cho JOIN khi bạn muốn cập nhật dựa trên một điều kiện đơn giản. Với subquery, bạn sẽ tránh được nhiều bảng phức tạp bằng cách chỉ định điều kiện trong truy vấn con.

Ví dụ sau minh họa cách dùng subquery để cập nhật bảng Sales với CustomerStatus mới nhất từ bảng Customers.

-- Update the CustomerStatus in the Sales table
UPDATE Sales
-- Set the CustomerStatus in Sales to corresponding value from the Customers table
SET CustomerStatus = (
    -- Fetch CustomerStatus from the Customers table for the matching CustomerID
    SELECT CustomerStatus 
    FROM Customers 
    WHERE Customers.CustomerID = Sales.CustomerID
)
-- Update rows in Sales where a matching CustomerID exists in the Customers table
WHERE EXISTS (
    -- Subquery to check if a matching CustomerID exists in the Customers table
    SELECT 1 
    FROM Customers 
    WHERE Customers.CustomerID = Sales.CustomerID
);

Câu lệnh MERGE

Câu lệnh MERGE là một lệnh SQL linh hoạt cho phép bạn thực hiện các thao tác UPDATE, INSERT hoặc DELETE trong một truy vấn duy nhất dựa trên việc so sánh hai bảng. MERGE hữu ích khi bạn cần đồng bộ dữ liệu giữa hai bảng, xử lý cả các hàng khớp và không khớp.

Việc dùng câu lệnh MERGE hữu ích vì nó cung cấp một cách gọn gàng để thực hiện nhiều thao tác trong một truy vấn. Cú pháp của MERGE như sau.

-- Merge data from the source table into the target table
MERGE INTO target_table AS target
USING source_table AS source
    -- Define the common key between the source and target tables
    ON target.common_column = source.common_column
-- When a match is found in both tables based on the common key
WHEN MATCHED THEN
    -- Update the target table's column with the corresponding value from the source table
    UPDATE SET target.column = source.column
-- When a row exists in the source table but not in the target table
WHEN NOT MATCHED THEN
    -- Insert the new data into the target table
    INSERT (column1, column2) 
    VALUES (source.column1, source.column2)
-- When a row exists in the target table but not in the source table
WHEN NOT MATCHED BY SOURCE THEN
    -- Delete the row from the target table
    DELETE;

Kết luận

Hiểu cách sử dụng UPDATE với JOIN trong SQL Server rất quan trọng để cập nhật dữ liệu chéo bảng hiệu quả trong cơ sở dữ liệu quan hệ. Kỹ thuật này cho phép bạn cập nhật bản ghi ở một bảng dựa trên dữ liệu khớp ở bảng khác, đảm bảo tính nhất quán và đồng bộ dữ liệu giữa các bảng liên quan. Áp dụng các kỹ thuật và thực hành tốt này có thể nâng cao hoạt động cơ sở dữ liệu của bạn, đảm bảo quản lý dữ liệu chính xác, hiệu quả và đáng tin cậy. 

Nếu bạn muốn nâng cao kỹ năng SQL, tôi khuyến nghị xem lộ trình kỹ năng SQL Server Fundamentals của DataCamp để cải thiện kỹ năng phân tích dữ liệu. Khóa học Writing Functions and Stored Procedures in SQL Server cũng sẽ giúp bạn học cách thao tác dữ liệu hiệu quả trong SQL Server. Nếu bạn đang chuẩn bị phỏng vấn SQL, tôi khuyên bạn đọc bài viết 20 Top SQL Joins Interview Questions để ôn luyện. Cuối cùng, nếu bạn muốn ứng dụng kỹ năng SQL trong sự nghiệp kỹ sư dữ liệu, hãy cân nhắc lộ trình nghề nghiệp Data Engineer in Python, kèm chứng chỉ sau khi hoàn thành.