SQLite UPSERT: INSERT OR REPLACE vs. SELECT with WHERE and INSERT

• If it exists: The UPSERT operation updates the existing row with the new values you provide.
• If it doesn't exist: A new row is inserted into the table with the provided values.

Key Distinction from INSERT or REPLACE:

• UPSERT: Ensures that only one row exists for the given unique identifier. It updates if it finds a match, otherwise inserts.
• INSERT: Inserts a new row regardless of whether a matching row exists, potentially creating duplicates.
• REPLACE: Deletes any existing row with the matching unique identifier before inserting a new row. This can lead to data loss if the existing row contained valuable information.

UPSERT in SQLite:

SQLite doesn't have a built-in UPSERT command. However, you can achieve UPSERT functionality using two common approaches:

1. INSERT OR REPLACE (Preferred):

   • This approach leverages the OR REPLACE clause within an INSERT statement.
   • If a row with the same unique identifier exists, it's replaced (updated) with the new values. Otherwise, a new row is inserted.

   INSERT OR REPLACE INTO your_table (column1, column2, ...)
   VALUES (value1, value2, ...)
   

2. SELECT with WHERE and INSERT (Alternative):

   • This method involves a two-step process:
     1. Use a SELECT statement to check if a row with the matching unique identifier exists.
     2. Based on the result:
        • If a row exists (indicated by a result from the SELECT), execute an UPDATE statement to modify the existing row.
        • If no row exists (no result from the SELECT), execute an INSERT statement to create a new row.

   -- Check if a row exists
   SELECT * FROM your_table WHERE unique_identifier = new_value_for_unique_identifier;
   
   -- If a row exists, update it
   IF EXISTS (SELECT * FROM your_table WHERE unique_identifier = new_value_for_unique_identifier)
   THEN
       UPDATE your_table
       SET column1 = new_value1, column2 = new_value2, ...
       WHERE unique_identifier = new_value_for_unique_identifier;
   ELSE
       -- Otherwise, insert a new row
       INSERT INTO your_table (column1, column2, ...)
       VALUES (value1, value2, ...);
   END IF;
   

Choosing the Right Approach:

• The INSERT OR REPLACE approach is generally preferred for its simplicity and efficiency.
• Use the SELECT with WHERE and INSERT approach if you need more complex logic or want to handle additional operations before or after the update/insert.

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE,
  email TEXT
);

-- Insert a new user (if username doesn't exist) or update an existing one (if username exists)
INSERT OR REPLACE INTO users (username, email)
VALUES ('john.doe', '[email protected]');

-- Update email for existing user 'jane.doe'
INSERT OR REPLACE INTO users (username, email)
VALUES ('jane.doe', '[email protected]');

SELECT with WHERE and INSERT:

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  name TEXT,
  stock INTEGER
);

-- Check if a product with ID 10 exists
SELECT * FROM products WHERE product_id = 10;

-- If product exists (result from SELECT), update stock
IF EXISTS (SELECT * FROM products WHERE product_id = 10)
THEN
  UPDATE products
  SET stock = stock + 5 -- Increase stock by 5
  WHERE product_id = 10;
ELSE
  -- Otherwise, insert a new product
  INSERT INTO products (product_id, name, stock)
  VALUES (10, 'New Product', 20);
END IF;

• You can create a trigger that fires on INSERT events for a table.
• Within the trigger, check if a row with the same unique identifier already exists.
  • If it exists, modify the NEW record (the record being inserted) to update the existing row's values.
  • If it doesn't exist, allow the INSERT to proceed normally.

Here's a basic example:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  email TEXT UNIQUE
);

CREATE TRIGGER update_or_insert_customer
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  -- Check if a customer with the same email exists
  SELECT * FROM customers WHERE email = NEW.email;

  IF EXISTS (SELECT * FROM customers WHERE email = NEW.email)
  THEN
    -- Update existing customer (modify NEW record)
    UPDATE customers
    SET name = NEW.name
    WHERE email = NEW.email;
  ELSE
    -- Allow insert to proceed
  END IF;
END;

-- Insert a new customer (if email doesn't exist) or update an existing one (if email exists)
INSERT INTO customers (name, email)
VALUES ('John Doe', '[email protected]');

MERGE statement (SQLite extension):

• SQLite doesn't natively support the MERGE statement, but you can enable it using the SQLITE_ENABLE_UPDATE_DELETE_MERGE extension.
• The MERGE statement allows you to combine INSERT and UPDATE operations in a single statement, similar to UPSERT.

Important points to consider:

• Triggers can introduce some complexity and might impact performance. Use them when you need additional logic beyond a simple update/insert.
• The MERGE extension requires enabling it, which might not be ideal in all environments.