MySQL INSERT vs UPDATE: Safe Upserts and Performance

If your leads, orders, or product feeds ever went missing or doubled overnight, odds are the problem wasn’t “marketing.” It was database write logic. MySQL INSERT and UPDATE decisions - plus the upsert patterns between them - are the backbone of dependable GTM and CRM pipelines. Get those right, and your dashboards, automations, and attribution stop arguing with each other and start printing usable revenue data.

Every form fill, webhook, and import is an INSERT. Every enrichment pass, lifecycle change, or attribution touch is an UPDATE. If these two operations aren’t thought through, you get duplicates, stale fields, and race conditions that quietly kill conversion rates. You don’t need a data team to prevent this - just clear write rules and a few MySQL patterns that make writes safe and predictable.

Downstream systems are only as good as upstream writes. If you accidentally overwrite a verified phone with a blank, your sales team can’t reach the prospect. If you “INSERT IGNORE” everything, you’ll mask real errors and think everything is fine - until someone asks where the last 3,000 leads went. The only real fix is to formalize when you insert new data, when you update existing records, and how you detect conflicts.

When revenue teams scale, write volume increases - bulk CSV imports, product feeds, ad platform syncs, event logs. That’s where performance and locking behavior start to matter. A naive UPDATE with no index can silently lock rows and slow your site or imports. Getting your INSERT/UPDATE discipline right early is cheaper than forensic cleanups after quarter-close.

INSERT is straightforward until it isn’t. The moment you add unique keys (you should), foreign keys (you probably should), and default values (you must), INSERT becomes the first gatekeeper of data quality.

Start with the basics. Use explicit column lists so schema changes don’t break old code, and validate inputs before you write. Prefer explicit NULL handling to let MySQL apply defaults intentionally - not accidentally.

INSERT INTO leads (external_id, email, first_name, source, created_at)
VALUES (?, ?, ?, ?, NOW());

Batch inserts reduce round trips and improve throughput. Instead of 1,000 single-row statements, send one statement with 1,000 values in chunks of, say, 2,000–5,000 depending on packet size and network.

INSERT INTO events (user_id, event_type, payload_json, occurred_at)
VALUES
  (123, 'page_view', '{...}', '2026-09-21 10:00:00'),
  (123, 'signup', '{...}', '2026-09-21 10:01:15');

Be cautious with INSERT IGNORE. It suppresses errors like duplicate keys, which sounds “safe” but often hides bugs. If a record collides with a unique constraint, you won’t know. Use it only when you expect duplicates and have downstream checks.

-- Risky if used by default: errors are swallowed
INSERT IGNORE INTO leads (external_id, email) VALUES (?, ?);

REPLACE is a trap for most business tables. It deletes the row and inserts a new one under the hood, which resets auto-increment and can blow away audit columns or related foreign keys. Unless you’re working with ephemeral caches, avoid it for core CRM data.

UPDATE is where data goes stale - or stays clean. Treat updates as surgical and idempotent. Update only what you mean to change, and guard them with WHERE conditions that rely on indexed columns.

Use targeted updates with joins to merge enrichment data, but ensure both sides are indexed on the join keys. Add updated_at columns that change automatically to help with syncs to downstream tools.

-- Update with join for enrichment
UPDATE leads l
JOIN enrichment e ON e.email = l.email
SET l.company_size = e.company_size,
    l.industry = e.industry,
    l.updated_at = NOW()
WHERE e.enriched_at > l.updated_at;  -- only if newer

Set SQL_SAFE_UPDATES in your dev and admin shells to avoid nuking entire tables by accident. In application code, always scope updates to a primary key or a unique key you trust.

-- In session: safer manual updates
SET SQL_SAFE_UPDATES = 1;  -- prevents UPDATEs without key in WHERE or LIMIT

UPDATE leads SET lifecycle_stage = 'MQL'
WHERE id = ?;  -- safe if id is primary key

Consider conditional updates that avoid clobbering good data. If the new value is blank or obviously worse, skip it. When merging partial profiles from multiple sources, a little “first non-null wins” logic goes a long way.

-- Do not overwrite a non-null phone with null
UPDATE leads
SET phone = COALESCE(?, phone),
    updated_at = NOW()
WHERE id = ?;

MySQL doesn’t have a MERGE keyword. The standard upsert is INSERT ... ON DUPLICATE KEY UPDATE (ODKU). It attempts an insert; if a unique or primary key collision occurs, it updates instead. That’s your one-liner for idempotent writes when you define proper unique constraints.

-- Create unique constraint on a natural key
ALTER TABLE leads ADD UNIQUE KEY uq_leads_email (email);

-- Upsert pattern
INSERT INTO leads (email, first_name, source, created_at, updated_at)
VALUES (?, ?, ?, NOW(), NOW())
ON DUPLICATE KEY UPDATE
  first_name = VALUES(first_name),
  source     = VALUES(source),
  updated_at = NOW();

ODKU has clear affected-rows semantics: 1 row affected for a true insert, 2 for an update of an existing row, and 0 if no change was made (for example, the values were identical). Use that signal to measure deduplication effectiveness or to trigger downstream flows only on real changes.

Be selective with updates during upsert. Overwriting a verified phone with a new one from a sketchy source hurts sales. You can gate field-level updates with CASE logic so that only higher-trust or fresher data wins.

INSERT INTO leads (email, phone, phone_source, updated_at)
VALUES (?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
  phone = CASE
    WHEN VALUES(phone_source) = 'call-center-verified' THEN VALUES(phone)
    WHEN phone IS NULL THEN VALUES(phone)
    ELSE phone
  END,
  phone_source = GREATEST(phone_source, VALUES(phone_source)),
  updated_at   = NOW();

If your natural keys are composite (e.g., external_id + system), define a composite unique index and use ODKU against it. This prevents collisions when the same external_id appears across multiple integrations.

ALTER TABLE contacts ADD UNIQUE KEY uq_contacts_ext (external_id, provider);

INSERT INTO contacts (external_id, provider, email, updated_at)
VALUES (?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
  email = COALESCE(VALUES(email), email),
  updated_at = NOW();

At higher volumes, your app will try to write the same record at the same time from different workers. Without a plan, you’ll get deadlocks or last-write-wins data loss. MySQL’s InnoDB engine, transactions, and unique constraints are your safety net.

Wrap related writes in a transaction when a set of changes must succeed or fail together. Keep transactions short to reduce lock contention. The default isolation level in MySQL is REPEATABLE READ; for some pipelines, READ COMMITTED may reduce contention - test before changing globally.

START TRANSACTION;
-- reserve or verify the record
SELECT id FROM leads WHERE email = ? FOR UPDATE;
-- write dependent rows now that the parent is locked
INSERT INTO lead_events (lead_id, event_type, occurred_at)
VALUES (?, 'enriched', NOW());
COMMIT;

Idempotency removes fear from retries. Generate a deterministic idempotency key per event (e.g., hash of provider + external_id + occurred_date) and make it unique. That way, retried inserts won’t duplicate data; they’ll no-op or upsert predictably.

ALTER TABLE events ADD UNIQUE KEY uq_events_idem (idem_key);

INSERT INTO events (idem_key, user_id, event_type, occurred_at)
VALUES (?, ?, 'page_view', ?)
ON DUPLICATE KEY UPDATE occurred_at = VALUES(occurred_at); -- or do nothing

When you see deadlocks: log the full queries, add proper indexes on join/where columns, and ensure all transactions lock rows in a consistent order. Most deadlocks are predictable once you look at the execution plans.

Clean writes start with schema. Choose primary keys, unique constraints, and defaults that reflect how your business actually identifies a lead or order. Then put the database in charge of what “unique” really means.

Primary keys: Auto-increment BIGINT is simple and fast. If you need cross-system uniqueness, consider storing UUIDs in BINARY(16) for compactness. For time-ordered writes and better index locality, UUIDv7 (if your generator supports it) stores nicely as BINARY(16) and orders by time. Whatever you pick, don’t expose your primary key choice to end users - treat it as plumbing.

Natural unique keys: Email alone is often too brittle. Use composite keys like (email, brand_id) or (external_id, provider). Create explicit unique indexes on these and always reference them in writes. Add NOT NULL defaults thoughtfully; a NULL where you expected an empty string can break uniqueness checks.

ALTER TABLE leads
  ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ADD UNIQUE KEY uq_leads_email_brand (email, brand_id);

-- Optional: generated hash for faster joins
ALTER TABLE leads
  ADD COLUMN email_sha BINARY(20) AS (UNHEX(SHA1(LOWER(TRIM(email))))) STORED,
  ADD INDEX ix_leads_email_sha (email_sha);

Performance tuning for writes is mostly about doing fewer, bigger, and indexed operations. The mechanics are simple; discipline is the hard part. Batch I/O, keep transactions short, and avoid full table scans during updates.

Use multi-row INSERT for normal ingestion, and use LOAD DATA INFILE for large CSV loads when you can control access, sanitize inputs, and meet security requirements. Chunk large UPDATEs with LIMIT to minimize lock time and avoid massive transaction logs.

-- Fast CSV import (server-side). Requires privileges and secure setup.
LOAD DATA INFILE '/var/lib/mysql-files/leads.csv'
INTO TABLE leads
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(email, first_name, source);

-- Chunked update to prevent long locks
UPDATE leads
SET lifecycle_stage = 'SQL'
WHERE lifecycle_stage = 'MQL'
ORDER BY id
LIMIT 1000;  -- repeat in a loop until 0 rows affected

Prepared statements are your friend. They reduce parsing overhead and protect you from injection. Turn off autocommit for short batching windows to cut fsyncs, but don’t leave long-running transactions open - they block purges and grow undo logs.

SET autocommit = 0;
-- batch of 500 inserts/updates
COMMIT;
SET autocommit = 1;

Leads rarely arrive clean. You’ll get three records for the same person from three platforms, each with different fields. Your write logic should consolidate these into a single “golden record” without manual cleanup every Friday.

Decide which fields are authoritative from which sources. For example, phone from call center beats phone from Facebook lead ads. Enrichment data should only fill gaps or upgrade accuracy, not randomize fields every day.

Implement dedupe passes that merge soft duplicates found by normalized keys (e.g., lowercase, trimmed emails; phone numbers normalized to E.164). Use UPDATE with joins to merge records into the survivor row, then archive or soft-delete the duplicate to preserve history.

-- Example: prefer verified phone; only fill blanks for others
INSERT INTO leads (email, phone, phone_trust, updated_at)
VALUES (?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
  phone = CASE
    WHEN VALUES(phone_trust) = 'verified' THEN VALUES(phone)
    WHEN phone IS NULL THEN VALUES(phone)
    ELSE phone
  END,
  phone_trust = GREATEST(phone_trust, VALUES(phone_trust)),
  updated_at  = NOW();

Every system eventually needs to answer: who changed what, and when? Add created_at and updated_at everywhere. For sensitive tables, store minimal change history, even if it’s just a JSON blob with changed fields and user/process ID.

Regulatory basics matter. When a deletion request arrives, don’t just NULL fields if your marketing tools can rehydrate them on the next sync. Implement true deletes or irreversible pseudonymization, and ensure your automations honor that choice end to end.

Make “do not contact” and consent fields first-class citizens. Don’t let a late-arriving insert from an ad platform override an opt-out. That’s a WHERE clause away from a costly mistake.

UPDATE leads
SET email_opt_in = 0, email = NULL, updated_at = NOW()
WHERE id = ? AND email_opt_in = 1; -- gate future sends on this flag

Most growth stacks push data across a queue (Webhook/API) → staging DB (MySQL) → CRM (HubSpot) → warehouse/reporting. INSERT/UPDATE rules live at the staging layer and protect your CRM from noise. You write cleanly to MySQL, then sync deltas to HubSpot by checking updated_at or change tables.

For marketing ops, two service categories matter: CRM write rules and workflow automation. If you’re standing up HubSpot and need the database glue - field mapping, dedupe, and safe write logic - that’s CRM implementation work. If you’re wiring data handlers from webhooks through MySQL into sequences and ads, that’s automation work. Explore how we approach this on our CRM and automation service pages when you’re ready to formalize it.

On the CRM side, see how disciplined schema and write logic translate into a reliable pipeline on our CRM implementation page. For cross-tool workflows (HubSpot, Make, n8n, LinkedIn/Ads), our marketing automation overview covers the operational patterns behind dependable syncs and triggers.

The operator model: ship the data layer without agency overhead

Quick context for why we care about this level of detail: Anton Chisliuk · AI Marketing Ops is an AI-first outsourced marketing operator - one senior builder who ships content, paid, outbound, and the data plumbing in between. No account-manager layer, just the person doing the work on the call and in the workflow. This model powered results like Cleverence going from 0 to 80K organic visits/month and ~2K inbound leads/month, because the database, content, and campaigns moved in lockstep. If you’re evaluating alternatives to traditional retainers, skim the about page for how the operator approach fits a 90-day build cycle and month-to-month after.

Here are a few drop-in patterns you can adapt. Each assumes sane indexes and not-null defaults where relevant. They’re battle-tested in marketing ops stacks where accuracy and speed both matter.

1) New inbound lead with first-touch attribution: create or update lead, preserve first_touch, update last_touch. Idempotent on (email, brand_id).

ALTER TABLE leads ADD UNIQUE KEY uq_leads_email_brand (email, brand_id);

INSERT INTO leads (
  email, brand_id, first_name, first_touch_source, last_touch_source, created_at, updated_at
) VALUES (
  ?, ?, ?, ?, ?, NOW(), NOW()
) ON DUPLICATE KEY UPDATE
  -- don't overwrite first touch if it already exists
  first_touch_source = COALESCE(first_touch_source, VALUES(first_touch_source)),
  -- always refresh last touch
  last_touch_source = VALUES(last_touch_source),
  first_name = COALESCE(VALUES(first_name), first_name),
  updated_at = NOW();

2) Event ingestion with idempotency: Upsert by unique IDEM key so retries don’t double count.

ALTER TABLE lead_events ADD UNIQUE KEY uq_lead_events_idem (idem_key);

INSERT INTO lead_events (idem_key, lead_id, event_type, payload_json, occurred_at, created_at)
VALUES (?, ?, ?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
  -- keep earliest occurrence if re-sent late; or choose latest depending on semantics
  occurred_at = LEAST(occurred_at, VALUES(occurred_at));

3) Progressive profile enrichment: Don’t downgrade good data. Only fill blanks or upgrade trust.

INSERT INTO leads (email, company, company_confidence, updated_at)
VALUES (?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE
  company = CASE
    WHEN company IS NULL THEN VALUES(company)
    WHEN VALUES(company_confidence) > company_confidence THEN VALUES(company)
    ELSE company
  END,
  company_confidence = GREATEST(company_confidence, VALUES(company_confidence)),
  updated_at = NOW();

Before shipping a new write path, run it against a sanitized subset with realistic duplicates and null patterns. Check row counts and affected_rows signals to confirm idempotency works. Log rejects with context - don’t just swallow them.

Phase rollouts. Gate new patterns behind a feature flag, and ship them to a small portion of traffic. Monitor deadlocks, lock wait timeouts, and slow query logs. If you’re introducing new unique keys, backfill and dedupe first or deploy as NOT ENFORCED until cleanup completes (MySQL lacks deferrable constraints, so sequence your changes carefully).

Instrumentation beats guesswork. Count: total writes, inserts vs upserts vs updates, duplicates prevented, write errors by type, and average latency. These become real business KPIs when mapped to lead integrity and sales velocity.

INSERT creates truth; UPDATE maintains it; a well-crafted upsert enforces it. If you formalize your unique keys, make idempotency explicit, and keep updates conditional and indexed, you’ll stop arguing about data integrity and start compounding outcomes across ads, content, and sales ops. That’s how marketing stops being a black box and turns into a reliable system.

Want a second set of senior eyes on your write paths, CRM schema, and automations? Book a free Growth Audit. We’ll map the fastest path to clean data and consistent pipeline in 90 days.

Article by Anton Chisliuk - Leads, sales, content, and systems - one senior operator.

- Should I use INSERT IGNORE or ON DUPLICATE KEY UPDATE for deduplication?

Use ON DUPLICATE KEY UPDATE with a proper unique index. INSERT IGNORE hides errors and tells you nothing about whether the row was new or skipped. With ODKU you can upsert deterministically, and affected_rows tells you if you inserted (1), updated (2), or no-op’d (0).

- Is REPLACE a good way to upsert?

Generally no for business tables. REPLACE deletes then inserts, which can reset auto-increment IDs, trigger cascades, and wipe audit columns. It’s fine for ephemeral caches or lookup tables without foreign keys, but not for CRM/lead data.

- How do I prevent overwriting good fields with worse ones?

Use conditional updates in your ODKU clause. Only fill blanks or accept higher-trust sources. CASE, COALESCE, GREATEST/LEAST, and updated_at comparisons help gate changes so you don’t downgrade verified data.

- What’s the best unique key for leads - email or external_id?

Usually a composite: (email, brand_id) or (external_id, provider). Email alone collides across brands, and external_id alone collides across providers. Use what guarantees uniqueness in your business, then index it.

- How do I make writes idempotent when events can retry?

Create an idem_key (e.g., a hash of provider + external_id + event_date) and add a unique index on it. Upsert on that key so retries don’t create duplicates. Decide if the retry should update timestamps or no-op and encode that in the ODKU clause.

- What isolation level should I use for high-write pipelines?

Start with the default REPEATABLE READ. If you see contention, test READ COMMITTED for specific sessions or services. Keep transactions short, index your WHERE/join columns, and lock rows in a consistent order to reduce deadlocks.

- When should I use LOAD DATA INFILE vs multi-row INSERT?

Use LOAD DATA INFILE for controlled bulk loads with secure file handling - it’s much faster. Use multi-row INSERT for application-level ingestion, batching 500–5,000 rows per statement depending on your environment. Both should target indexed, deduped schemas.