upsert：行を挿入する、すでにあれば更新する

1

-- Insert, or update the row that is already there.
INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '2026-01-01 00:00:00')
ON CONFLICT (customer_id, product_id)
DO UPDATE SET added_at = excluded.added_at;

2

SELECT customer_id, product_id, added_at
FROM wishlists
WHERE customer_id = 1 AND product_id = 288;

3

-- "Insert if it is not there, otherwise leave it alone." No error.
INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '1999-12-31 00:00:00')
ON CONFLICT (customer_id, product_id) DO NOTHING;

PostgreSQL

INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '2026-01-01 00:00:00')
ON CONFLICT (customer_id, product_id)
DO UPDATE SET added_at = EXCLUDED.added_at;

-- DO NOTHING is the same idea as SQLite's.
INSERT INTO wishlists (customer_id, product_id)
VALUES (1, 288)
ON CONFLICT DO NOTHING;

MySQL / MariaDB

-- MySQL 8.0.20 and later: alias the new row instead of VALUES().
INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '2026-01-01 00:00:00') AS new
ON DUPLICATE KEY UPDATE added_at = new.added_at;

-- Older MySQL and MariaDB:
INSERT INTO wishlists (customer_id, product_id, added_at)
VALUES (1, 288, '2026-01-01 00:00:00')
ON DUPLICATE KEY UPDATE added_at = VALUES(added_at);

SQL Server / Oracle

MERGE INTO wishlists AS target
USING (SELECT 1 AS customer_id, 288 AS product_id, '2026-01-01' AS added_at) AS source
    ON target.customer_id = source.customer_id
   AND target.product_id  = source.product_id
WHEN MATCHED THEN
  UPDATE SET added_at = source.added_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, product_id, added_at)
  VALUES (source.customer_id, source.product_id, source.added_at);

4

-- REPLACE deletes the conflicting row and inserts a new one, so
-- added_at is not kept — it falls back to its default. Run the check
-- above again afterwards and compare.
INSERT OR REPLACE INTO wishlists (customer_id, product_id)
VALUES (1, 288);