Database Changes SQL Topic exerciseHardVerified answerSQLite + PostgreSQL live · 3 guided

Upsert a Retry-Safe Account Sync

Upsert account 2 so Northstar Growth is enterprise, active, and has a 14000 credit limit.

  • UPSERT
  • Idempotency

Exercise brief

Understand the request

Integration engineering A retry-safe sync must update an existing business key without creating a duplicate account or requiring a separate existence check.

The CRM sync may deliver the same approved Northstar account more than once and must converge on one current row.

Return

  • Converge on exactly one account 2 row with the approved values.

Constraints

  • Use one atomic upsert statement.
  • Use account_id as the conflict key.
  • Preserve every other account.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

accounts

  • account_idINTEGER
  • account_nameVARCHAR(100)
  • tierVARCHAR(20)
  • statusVARCHAR(20)
  • credit_limitINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

An upsert combines the insert and conflict-update paths around one unique key.

Hint 2

For SQLite and PostgreSQL, target account_id with ON CONFLICT and read incoming values from excluded.

Hint 3

INSERT INTO accounts (...) VALUES (...) ON CONFLICT (account_id) DO UPDATE SET ...;

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
INSERT INTO accounts (account_id, account_name, tier, status, credit_limit)
VALUES (2, 'Northstar Growth', 'enterprise', 'active', 14000)
ON CONFLICT (account_id) DO UPDATE SET
  account_name = excluded.account_name,
  tier = excluded.tier,
  status = excluded.status,
  credit_limit = excluded.credit_limit;

Why this works

A single conflict-aware statement makes delivery retries converge on one row and avoids the race between a separate existence check and later write.

Success check

Account 2 has the approved profile, the accounts table still contains four rows, and a retry produces the same final state.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.