This page needs a newer browser to run its tools. You can still read everything here.

SQL (PostgreSQL, MySQL, SQLite) Module 10

Changing data: INSERT, UPDATE, DELETE and UPSERT interview questions

Interview questions on “Changing data: INSERT, UPDATE, DELETE and UPSERT”: warm-ups, then what each harder question tests and how a good answer starts.

16 questions: 5 warm-ups and 11 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher

What is the difference between leaving a column out of an INSERT and inserting NULL into it?

Answered in the lesson INSERT, defaults and getting generated keys
Fresher

How do you get the id of a row you have just inserted, in PostgreSQL, MySQL and SQLite?

Answered in the lesson INSERT, defaults and getting generated keys
Fresher

What happens when an UPDATE or DELETE has no WHERE clause, and how do you check a change before it becomes permanent?

Answered in the lesson UPDATE and DELETE: changing rows safely
Fresher to mid

What is an upsert, and why does it need a unique constraint?

Answered in the lesson UPSERT and MERGE: insert or update in one step
Fresher to mid

What is the difference between COPY and \copy in PostgreSQL?

Answered in the lesson Bulk loading and exporting CSV

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Fresher to mid

An order service inserts an order, reads its id with SELECT max(order_id) FROM orders, then inserts the order's items with that id. Every test passes, but in production some items end up on other customers' orders. Explain why, and how you would write it in PostgreSQL and in MySQL.
What it tests

Races between two statements, how each engine hands a generated key back to the connection that inserted the row, and keeping an order and its items together in one transaction.

First step

Put two requests side by side. A inserts order 501, B inserts order 502, and then both run SELECT max(order_id) and read 502, so A's items land on B's order. The key has to come from the INSERT itself, through RETURNING or LAST_INSERT_ID(), never from a later look at the whole table.
Follow-ups
- Would running both statements in one transaction make max(order_id) safe?
- The items insert fails halfway through. What is left in the tables, and how do you prevent it?
- How do you insert 1,000 orders with their items without 1,000 round trips?
From the lesson INSERT, defaults and getting generated keys
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

On MySQL 8.4, one INSERT adds 500 rows to a table with an AUTO_INCREMENT key, and the application needs all 500 new keys to insert child rows. LAST_INSERT_ID() returns 10001. May the code assume the keys are 10001 to 10500? How do you get them reliably?
What it tests

What LAST_INSERT_ID() reports after a multi-row INSERT, what InnoDB promises about the keys of one statement in each auto-increment lock mode, and a key mapping that does not depend on those promises.

First step

Start from what the manual promises. LAST_INSERT_ID() is the key of the first row of the statement. In the default interleaved lock mode, the keys of one plain multi-row INSERT have no gaps only while nothing but such simple inserts runs; a bulk insert running at the same time, or a mixed-mode insert, breaks that. So map each row by a natural key instead of adding numbers.
Follow-ups
- What changes when innodb_autoinc_lock_mode is 1?
- The batch is an INSERT … ON DUPLICATE KEY UPDATE. What happens to the keys?
- How would PostgreSQL and SQLite hand back all 500 keys?
From the lesson INSERT, defaults and getting generated keys
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

A bug charged 1,240 orders twice. You must set refund_due to true on exactly those orders in the production database. Walk through how you write, check and run the change.
What it tests

The safe-change routine: defining the rows from data, previewing, rehearsing in a transaction with the count checked, keeping a record of what changed, and making the fix safe to run twice.

First step

Define the 1,240 orders from data, not from memory: for example the orders with two successful charges in the payments table, grouped by order with HAVING count(*) = 2. Run that SELECT, check that it returns 1,240 rows, and save the order ids in a table of their own before anything changes.
Follow-ups
- The rehearsal reports 1,243 rows. What do you do?
- How do you make sure the fix can be undone tomorrow?
- The orders table has 80 million rows and is busy. Does that change the plan?
From the lesson UPDATE and DELETE: changing rows safely
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid

After an UPDATE … FROM copied delivery times from the courier's events table, about 3 % of orders show a delivery time a day off. The statement reported no error. What happened, and how do you fix the statement and the data?
What it tests

Joins that find several source rows for one target row, what each engine does with them, finding duplicates in a feed, and repairing only the damaged rows from the source.

First step

Look at what the join fed the update before you look at the statement. Count the source rows per order with SELECT order_id, count() FROM courier_events GROUP BY order_id HAVING count() > 1, and compare the orders it lists with the ones whose delivery time is a day off. A statement that reported no error can still have been given the wrong rows.
Follow-ups
- Which event should win, and how do you write that?
- How do you find and repair only the orders that got a wrong time?
- Would MERGE have behaved differently?
From the lesson UPDATE and DELETE: changing rows safely
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A MySQL table of 120 million log rows grows by 2 million a day. You must remove the 30 million rows older than 90 days, and keep it at 90 days afterwards, while the application writes to it all the time. How do you do it, and how long will it take?
What it tests

Batched deletes and how long locks are held, the index behind the delete condition, replication lag, how space is reused, and a design that makes old data cheap to drop.

First step

Start from the numbers and the constraints: 30 million of 120 million rows must go while 2 million arrive a day, and the application never stops writing. Check which index serves the condition on created_at, then decide how many rows one transaction may delete before its locks and its effect on the replicas start to hurt.
Follow-ups
- How do you keep the table at 90 days afterwards?
- Replicas fall behind during the delete. What do you change?
- The table did not get smaller on disk. Why?
From the lesson UPDATE and DELETE: changing rows safely
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid

Two app servers process "set stock" messages for the same product at the same moment. The code reads the stock row, then inserts or updates it. Sometimes a product ends up with two stock rows, sometimes with an older quantity. Diagnose both problems and fix them in PostgreSQL and MySQL.
What it tests

Check-then-act races, the unique key as the arbiter, an upsert against locking, and a version in each message so that a late message cannot win.

First step

Separate the two symptoms. Two rows means nothing unique stops a second insert: add a primary key on the product and replace the read-then-write with one upsert. An older quantity means messages are applied out of order: carry a version in each message and update only when it is newer.
Follow-ups
- Would SELECT … FOR UPDATE fix the first symptom?
- How do you remove the duplicate rows before adding the key?
- What changes if the messages carry deltas instead of quantities?
From the lesson UPSERT and MERGE: insert or update in one step
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A nightly CRM sync upserts customers into a MySQL table with a UNIQUE email and a UNIQUE phone, using INSERT … ON DUPLICATE KEY UPDATE. Support reports that one customer's row now carries another customer's name. Explain how that happens and redesign the sync.
What it tests

How ON DUPLICATE KEY UPDATE chooses a row when several unique keys conflict, why REPLACE is worse, and building a sync around one stable identifier.

First step

Look at one damaged row. The CRM row carries one customer's email and another customer's phone, for example because a number changed hands. Both unique keys conflict, with different rows, and MySQL updates one of them. The sync matches on two keys at once when it should match on one.
Follow-ups
- What would REPLACE have done with the same row?
- How do you find the rows the sync has already damaged?
- How would PostgreSQL behave with the same sync?
From the lesson UPSERT and MERGE: insert or update in one step
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

Every night a 500,000-row product feed must be applied to a 20-million-row PostgreSQL table: new products inserted, changed ones updated, products missing from the feed marked discontinued. MERGE or INSERT … ON CONFLICT? Plan the job, including a failure halfway and a second run.
What it tests

MERGE against ON CONFLICT under concurrency, staging and validating a feed, guarding destructive branches, making the job safe to repeat, and keeping transactions and locks short.

First step

Load the feed into a staging table with COPY and validate it there: one row per sku, sane prices, and a row count close to the last good feed. Only then apply it. MERGE fits three branches, but "missing from the feed" is computed against all 20 million rows, so count each branch before committing.
Follow-ups
- How do you stop a truncated feed of 50 rows from discontinuing 19.9 million products?
- Another service inserts products while the job runs. What can go wrong?
- How long are rows locked, and how do you keep that short?
From the lesson UPSERT and MERGE: insert or update in one step
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid

A nightly MySQL job loads a 2-million-row supplier CSV with LOAD DATA LOCAL INFILE. A week later, finance finds 3,100 products priced at 0. No load ever failed. What happened, and how do you make the load fail loudly?
What it tests

How LOCAL changes LOAD DATA's error handling, why warnings are not errors, and a load that checks the rows before it writes them.

First step

Run one load by hand and then SHOW WARNINGS. With LOCAL, MySQL treats data errors as warnings even in strict mode: a price such as 'n/a' or '1,499' becomes 0, and a repeated key is skipped. The statement reports success with warnings that the job never reads.
Follow-ups
- Why does strict mode not stop it?
- How do you repair the 3,100 prices?
- What would PostgreSQL's COPY do with the same file?
From the lesson Bulk loading and exporting CSV
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

You must load 50 million rows from CSV files into a PostgreSQL table that serves live traffic and has three indexes and a foreign key. Plan the load, and estimate how long it will take.
What it tests

COPY and staging, what indexes and foreign key checks cost during a load, transaction size, WAL and replicas, and estimating from a measured sample instead of guessing.

First step

Measure before planning. COPY one million rows of the real file into a staging table on the same hardware, then into a copy of the target with its indexes. If that takes 10 s and 40 s, the bare copy of 50 million rows is about 500 s and the indexed one about 2,000 s, and the plan follows from that gap.
Follow-ups
- Can you drop the indexes of a table that serves traffic?
- Why not load all 50 million rows in one transaction?
- What do you run after the load?
From the lesson Bulk loading and exporting CSV
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Your service exports customer records to CSV for the support team, who open the files in a spreadsheet. A security review flags the export. What risks does it find, and what do you change?
What it tests

Formula injection in spreadsheets, quoting by RFC 4180, limiting the personal data an export carries, and keeping file privileges away from application accounts.

First step

List what the file can do once it leaves the database. A name that starts with = runs as a formula in a spreadsheet, a comma or a quote in a field shifts columns, and every exported row of personal data is a copy nobody controls. Fix the cells first, then reduce what the file contains and who can produce it.
Follow-ups
- Is a leading apostrophe enough?
- The export is written with SELECT … INTO OUTFILE by the application's database account. What is wrong with that?
- How do you stop exported files from piling up?
From the lesson Bulk loading and exporting CSV
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

INSERT, defaults and getting generated keys Free lesson, Pro answers: 20 minutes
UPDATE and DELETE: changing rows safely Free core, Pro depth: 7 of 20 minutes free
UPSERT and MERGE: insert or update in one step Free core, Pro depth: 8 of 20 minutes free
Bulk loading and exporting CSV Free core, Pro depth: 7 of 20 minutes free

All SQL (PostgreSQL, MySQL, SQLite) interview questions · The SQL (PostgreSQL, MySQL, SQLite) track

Warm-up questions

What is the difference between leaving a column out of an INSERT and inserting NULL into it?

How do you get the id of a row you have just inserted, in PostgreSQL, MySQL and SQLite?

What happens when an UPDATE or DELETE has no WHERE clause, and how do you check a change before it becomes permanent?

What is an upsert, and why does it need a unique constraint?

What is the difference between COPY and \copy in PostgreSQL?

Questions with model answers

An order service inserts an order, reads its id with SELECT max(order_id) FROM orders, then inserts the order's items with that id. Every test passes, but in production some items end up on other customers' orders. Explain why, and how you would write it in PostgreSQL and in MySQL.

On MySQL 8.4, one INSERT adds 500 rows to a table with an AUTO_INCREMENT key, and the application needs all 500 new keys to insert child rows. LAST_INSERT_ID() returns 10001. May the code assume the keys are 10001 to 10500? How do you get them reliably?

A bug charged 1,240 orders twice. You must set refund_due to true on exactly those orders in the production database. Walk through how you write, check and run the change.

After an UPDATE … FROM copied delivery times from the courier's events table, about 3 % of orders show a delivery time a day off. The statement reported no error. What happened, and how do you fix the statement and the data?

A MySQL table of 120 million log rows grows by 2 million a day. You must remove the 30 million rows older than 90 days, and keep it at 90 days afterwards, while the application writes to it all the time. How do you do it, and how long will it take?

Two app servers process "set stock" messages for the same product at the same moment. The code reads the stock row, then inserts or updates it. Sometimes a product ends up with two stock rows, sometimes with an older quantity. Diagnose both problems and fix them in PostgreSQL and MySQL.

A nightly CRM sync upserts customers into a MySQL table with a UNIQUE email and a UNIQUE phone, using INSERT … ON DUPLICATE KEY UPDATE. Support reports that one customer's row now carries another customer's name. Explain how that happens and redesign the sync.

Every night a 500,000-row product feed must be applied to a 20-million-row PostgreSQL table: new products inserted, changed ones updated, products missing from the feed marked discontinued. MERGE or INSERT … ON CONFLICT? Plan the job, including a failure halfway and a second run.

A nightly MySQL job loads a 2-million-row supplier CSV with LOAD DATA LOCAL INFILE. A week later, finance finds 3,100 products priced at 0. No load ever failed. What happened, and how do you make the load fail loudly?

You must load 50 million rows from CSV files into a PostgreSQL table that serves live traffic and has three indexes and a foreign key. Plan the load, and estimate how long it will take.

Your service exports customer records to CSV for the support team, who open the files in a spreadsheet. A security review flags the export. What risks does it find, and what do you change?