How does UPSERT work in SQL (ON CONFLICT, ON DUPLICATE KEY, MERGE)?
How does UPSERT work in SQL (ON CONFLICT, ON DUPLICATE KEY, MERGE)?
An upsert inserts a row, or updates the existing row if the key already exists, in one atomic statement. It replaces the racy "SELECT, then INSERT or UPDATE" pattern, where two sessions can both find nothing and both insert.
INSERT INTO page_views (page_id, view_date, views)
VALUES (1, CURRENT_DATE, 1)
ON CONFLICT (page_id, view_date)
DO UPDATE SET views = page_views.views + EXCLUDED.views;
INSERT INTO tags (name) VALUES ('sql') ON CONFLICT (name) DO NOTHING; -- ignore duplicates
EXCLUDED is the row that would have been inserted. The conflict target must match a unique index or constraint.
INSERT INTO page_views (page_id, view_date, views)
VALUES (1, CURDATE(), 1) AS new
ON DUPLICATE KEY UPDATE views = views + new.views;
The row alias (AS new, MySQL 8.0.19+) replaces the older VALUES(views) function, which is deprecated. It reacts to any unique index conflict, not one you name. INSERT IGNORE skips duplicates but also hides other errors.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.