Editing Data and Tables

Part 1: Concept Notes: INSERT, UPDATE, DELETE, ALTER, TRUNCATE

Introduction

What is it? Every real application needs to do more than just read data, it needs to add new information, fix existing records, and remove data that's no longer needed. In SQL, these write operations fall into two groups:

• DML (Data Manipulation Language): INSERT, UPDATE, and DELETE. These change the data inside a table's rows.
• DDL (Data Definition Language): ALTER and TRUNCATE. These change the structure of a table, or wipe it clean, rather than working with individual rows of data.

Why do we need it? A table that could only be read from, never written to, would be useless for any real system. A hiring system needs to INSERT a new employee. A payroll system needs to UPDATE salaries after a raise. A cleanup job needs to DELETE outdated records. And as business needs change, tables themselves need to evolve too, which is exactly what ALTER is for.

Real-world analogy Think of a filing cabinet in an office. INSERT is like adding a brand new folder to the cabinet. UPDATE is like opening an existing folder and correcting some information inside it. DELETE is like pulling a folder out entirely and shredding it. ALTER is like redesigning the folder template itself, adding a new field to every folder, renaming a section, or changing what kind of information a field is allowed to hold. TRUNCATE is like emptying every folder in the cabinet at once, but keeping the empty cabinet and its labeled drawers in place.

Business use case A very common real-world case is a policy change. If a company decides that, starting today, all new hires must have their salary reviewed manually before being marked "active," that might require an UPDATE across existing rows, an ALTER to add a new tracking column, and ongoing INSERTs that follow the new rule going forward. These commands work together constantly in real systems.

This chapter also introduces one especially important safety concept: the WHERE clause. Getting comfortable with exactly how WHERE controls which rows are affected is one of the most important habits to build, since forgetting it (or getting it wrong) is one of the most common causes of serious, hard-to-undo mistakes in real production databases.

Dataset Used

Most examples in this document build on a single evolving employees table, so you can see how the exact same table changes step by step as different commands are applied to it.

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department VARCHAR(50) NULL,
  salary DECIMAL(10,2) NOT NULL,
  hire_date DATE NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT chk_salary_nonnegative CHECK (salary >= 0)
);

A few things worth noticing about this schema before diving into examples:

• emp_id is the primary key, so it must be unique and cannot be NULL.
• name, salary, hire_date, and is_active are all marked NOT NULL, meaning every row must provide a value for these.
• department is allowed to be NULL, meaning it's completely optional for a row to have a department at all.
• There's a CHECK constraint ensuring salary can never be stored as a negative number.
• is_active defaults to TRUE automatically if not specified during an INSERT.

Syntax

Here's a quick reference of every command covered in this document:

Mental Model

Picture a spreadsheet that represents your table.

• INSERT adds a brand new row at the bottom of the spreadsheet.
• UPDATE goes to specific existing rows and overwrites specific cells with new values.
• DELETE removes specific entire rows from the spreadsheet.
• ALTER changes the spreadsheet's column headers themselves, adding a new column, removing one, renaming one, or changing what kind of data a column is allowed to hold.
• TRUNCATE clears out every single row instantly, but leaves the column headers (the structure) completely intact, ready for new data.

The single most important habit to build here is this: UPDATE and DELETE without a WHERE clause affect every single row in the table, not just "the one you were thinking about." Always pause and double check your WHERE clause before running either of these commands against real data.

How SQL Executes It

For INSERT, UPDATE, and DELETE, the database generally follows this sequence:

1. Checks that the table and referenced columns actually exist.
2. Validates the data against the table's constraints (data types, NOT NULL rules, CHECK constraints, uniqueness of primary keys, and so on).
3. If validation passes, the change is applied: a new row is appended (INSERT), existing matching rows are modified (UPDATE), or existing matching rows are removed (DELETE).
4. If validation fails at any point (like trying to insert a duplicate primary key, or a negative salary that violates a CHECK constraint), the entire operation is rejected, and no partial change is made.

For ALTER, the database needs to modify the table's underlying structure, which can sometimes require rewriting the whole table internally, depending on the specific change and the database engine being used. This is part of why ALTER operations, especially on very large tables, can take noticeably longer than a simple UPDATE or DELETE.

TRUNCATE works differently from DELETE internally. Instead of removing rows one at a time, it essentially deallocates the data pages that store the table's rows all at once, which is why it's dramatically faster on large tables.

1. INSERT: Adding New Rows

Example 1: Inserting a single new employee

Suppose a new employee has just been hired, and needs to be added to the table:

• emp_id: 301
• name: Neha Sharma
• department: Sales
• salary: 55000.00
• hire_date: 2024-03-11
• is_active: TRUE

INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active) VALUES
(301, 'Neha Sharma', 'Sales', 55000.00, '2024-03-11', TRUE);

Best practice: always specify column names explicitly

If you don't list out the column names before VALUES, the database falls back to relying on the table's actual column order. This is risky, because if the table's schema ever changes later (a column gets added, removed, or reordered), your old INSERT statements can silently insert values into the wrong columns, or fail unexpectedly.

What happens when this INSERT runs

1. The database checks that the employees table exists and that all the referenced columns (emp_id, name, department, salary, hire_date, is_active) are valid columns on that table.
2. It validates data types and constraints:
   • emp_id must be unique (since it's the primary key) and not NULL.
   • salary must be a valid decimal value, and must satisfy the CHECK constraint (non-negative).
   • hire_date must follow a proper date format.
   • is_active must be a valid boolean value.
3. After all validation passes, the database appends a new row to the table with the provided values.

Example 2: Inserting multiple rows at once

You can insert several rows in a single statement by separating each set of values with commas, which is far more efficient than writing six separate INSERT statements:

INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active) VALUES
(302, 'Ishan Gupta',  'Engineering' ,110000.00, '2023-08-20', TRUE),
(303, 'Priya Singh',  'Support'     ,45000.00, '2024-07-01', TRUE),
(304, 'Karan Joshi',  'Engineering' ,95000.00, '2022-02-14', FALSE),
(305, 'Meera Nair',   ''            ,50000.00, '2025-03-01', TRUE),
(306, 'Omar Farooq',  NULL          ,62000.00, '2025-04-18', TRUE),
(307, 'Ananya Das',   'Finance'     ,72000.00, '2024-10-05', TRUE);

Table after all inserts

Notice row 305 has an empty string '' for department, while row 306 has an actual NULL value. These are two completely different things, exactly like the NULL-vs-empty-string distinction covered in the earlier CASE Expression notes: an empty string means "a department was provided, but it's blank," while NULL means "no department value was given at all."

UPSERT: Insert or Update Automatically

What is it? UPSERT is a database operation that inserts a new record if it doesn't already exist, or updates the existing record if it does, based on a unique constraint or primary key. In simple terms: "insert the row if it's new, otherwise update the existing one."

Example: Receiving updated information for an existing employee

Suppose the employees table already contains this record:

Now, an HR system receives updated information for emp_id 301 from a new data feed, but the system doesn't know in advance whether this employee already exists in the table or not:

INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active)
VALUES (301, 'Raj Sharma', 'Eng', 57000.00, '2029-01-01', TRUE) AS new_record
ON DUPLICATE KEY UPDATE
  name        = new_record.name,
  department  = new_record.department,
  salary      = new_record.salary,
  hire_date   = new_record.hire_date,
  is_active   = new_record.is_active;

What happens

Since emp_id = 301 already exists (it's the primary key, and 301 is already taken), the database detects this conflict and, instead of failing or creating a duplicate, runs the UPDATE portion instead, overwriting the existing row's values with the new ones.

Resulting table

Why this matters: UPSERT is extremely common in data pipelines and sync jobs, where the same incoming data feed might contain a mix of genuinely new records and updates to existing ones, and you don't want to write separate logic to check "does this already exist?" before deciding whether to INSERT or UPDATE.

2. UPDATE: Modifying Existing Rows

Safety note: The full-table and destructive statements below are educational examples. Run them only against disposable data, or after verifying a backup and transaction plan. Preview an UPDATE or DELETE with the same WHERE condition before changing real data.

UPDATE without WHERE (dangerous)

UPDATE employees
SET department = 'HR';

Result

This query updates every single row in the table. Since no WHERE clause is used, every employee, regardless of their original department, is now assigned to HR. This is considered dangerous in production systems, and is exactly the kind of mistake that can cause real, hard-to-reverse damage if run accidentally.

UPDATE with WHERE (the safe, recommended approach)

UPDATE employees
SET department = 'Eng'
WHERE emp_id = 305;

Result

The WHERE clause restricts the update to exactly one row. Only the employee with emp_id = 305 is modified, every other row remains untouched.

Updating multiple columns at once

UPDATE employees
SET department = 'HR',
    salary = 65000
WHERE emp_id = 305;

Result

Multiple columns can be updated in a single UPDATE statement, separated by commas after SET. Here, both department and salary are modified together in one atomic operation, which is more consistent and efficient than running two separate UPDATE statements.

Conditional mass UPDATE

UPDATE employees
SET salary = salary * 1.5
WHERE hire_date >= '2024-01-01';

Result

This query updates multiple rows conditionally, only employees hired on or after January 1st, 2024 are affected. The salary increase (50%) is calculated directly from each row's existing salary value using salary * 1.5, rather than being set to one fixed number for everyone. Notice that emp_id 302 and 304, hired before 2024, kept their original salary untouched.

3. DELETE: Removing Rows

Deleting a single row

DELETE FROM employees
WHERE emp_id = 305;

The WHERE clause restricts deletion to exactly one specific row. Only the employee with emp_id = 305 is removed, all other rows remain unchanged.

Deleting multiple rows using IN

DELETE FROM employees
WHERE emp_id IN (301, 302);

The IN operator allows deletion of multiple specific rows in a single query. Employees with emp_id 301 and 302 are removed together. This is commonly used for batch deletions, like cleaning up a known list of test records or duplicate entries.

Deleting every row (no WHERE clause)

DELETE FROM employees;

Since there's no WHERE clause, every row in the employees table is deleted. However, the table structure itself remains intact, the empty table is still there, ready to accept new rows, it's just that every existing row is now gone.

4. ALTER: Modifying Table Structure

DDL safety: ALTER and DROP COLUMN examples change schema or destroy stored values. Test them on disposable data and take a verified backup before applying them to an important table. In MySQL, many DDL statements cause an implicit commit, so a surrounding transaction does not make them freely reversible.

ALTER changes the structure of an existing table, without deleting the table or its remaining data.

Assume this base table before any ALTER operations:

Add a new column

ALTER TABLE employees
ADD COLUMN temp_check INT;

Result

A new column temp_check is added to the table. Existing rows are not deleted or disturbed. The new column simply contains NULL for every existing row by default, since none of them have a value for this brand new column yet.

Backfilling data using UPDATE

UPDATE employees
SET temp_check = 10;

Result

This UPDATE fills in the newly added column for all existing rows. Backfilling like this is an extremely common practice right after adding a new column in a production system, since you usually want existing rows to have a sensible default value, not just NULL.

Dropping a column

ALTER TABLE employees
DROP COLUMN temp_check;

Result

The column temp_check is permanently removed. All data stored in that column is lost entirely, there's no undo for this once it runs. Every other column and row remains unchanged.

Renaming a table

ALTER TABLE employees
RENAME TO employees_v2;

The table's name changes from employees to employees_v2. All the data and structure inside it remain exactly the same, nothing about the rows or columns changes. From this point onward, every future query needs to reference employees_v2 instead of the old name.

Renaming a column

ALTER TABLE employees_v2
RENAME COLUMN temp_check TO temp_check_v2;

(This assumes the column still exists at the time of renaming.) The column's name changes without affecting any of the data stored inside it. This is typically done purely for clarity or to keep naming conventions consistent across a codebase.

CHANGE clause

The CHANGE clause renames a column and can change its datatype and constraints at the same time, all in one statement.

• The column name must be specified twice (the old name, then the new name).
• The datatype must be redefined, even if it's staying the same.
• It can also modify constraints, like NOT NULL or DEFAULT values.

Syntax

ALTER TABLE table_name
CHANGE old_column_name new_column_name new_data_type;

Example

ALTER TABLE employees
CHANGE salary monthly_salary DECIMAL(10,2);

This renames salary to monthly_salary, and redefines (or changes) its datatype at the same time.

MODIFY clause

The MODIFY clause changes the datatype or constraints of a column while keeping its name exactly the same.

• The column name stays unchanged.
• It's used only for datatype or constraint updates, not renaming.
• The datatype must still be explicitly specified.

Syntax

ALTER TABLE table_name
MODIFY column_name new_data_type;

Example

ALTER TABLE employees
MODIFY salary DECIMAL(12,2);

The column name stays salary, only its datatype or constraints are updated (in this case, allowing for larger salary values with more digits).

When to use which: if you only want to change the datatype or constraints, use MODIFY. Only use CHANGE when renaming the column is explicitly required, since many production bugs happen due to accidental column renaming caused by CHANGE being used when MODIFY would have been safer and simpler.

5. TRUNCATE: Instantly Clearing a Table

Destructive example: Use the TRUNCATE below only on a disposable table or after a verified backup. MySQL treats TRUNCATE as DDL and it causes an implicit commit, so you cannot rely on ROLLBACK to restore the rows.

TRUNCATE is a Data Definition Language (DDL) operation used to remove all rows from a table quickly and efficiently. It's used to:

• Remove all rows from a table
• Reset the table to a completely empty state
• Perform this much faster than a DELETE without a WHERE clause would

TRUNCATE TABLE employees;

Result

The table still exists, with all its columns and structure intact, but it now contains zero records.

DELETE vs. TRUNCATE vs. DROP

Deep Dive

Internal behavior INSERT, UPDATE, and DELETE all operate row by row conceptually, checking constraints and applying changes to matched rows individually (even if the database optimizes this internally for bulk operations). TRUNCATE, by contrast, doesn't process rows individually at all, it works by deallocating the storage space the table's rows were using, which is why it's dramatically faster but also why it typically can't be filtered with a WHERE clause, and often can't be easily rolled back the same way a DELETE can in some databases.

NULL handling When inserting a row, any column not explicitly given a value (and not marked NOT NULL) will store NULL by default, unless the column has a DEFAULT value defined, like is_active BOOLEAN NOT NULL DEFAULT TRUE in the employees table, which automatically becomes TRUE if not specified during INSERT. This is different from providing an empty string '', which is a real, non-NULL value, just an empty one, exactly as covered in the earlier CASE Expression notes.

Performance considerations UPDATE and DELETE statements with a WHERE clause on an indexed column (like a primary key) are typically very fast, since the database can quickly locate exactly which rows to touch. Without an index on the WHERE condition's column, the database may need to scan the entire table to find matching rows, which can be slow on very large tables. ALTER operations, especially ones that change a column's datatype or add a new column with a default value on an existing large table, can sometimes lock the table or take a long time, depending on the database engine, since it may need to rewrite existing rows to conform to the new structure.

Database behavior The exact syntax for some of these commands varies between databases. ON DUPLICATE KEY UPDATE (used for UPSERT) is MySQL-specific; PostgreSQL uses INSERT ... ON CONFLICT ... DO UPDATE instead, and SQL Server has its own MERGE statement for similar behavior. The CHANGE clause for ALTER is also MySQL-specific; PostgreSQL and other databases typically use separate statements like RENAME COLUMN and ALTER COLUMN ... TYPE instead of one combined CHANGE clause.

Real-world Use Cases

• Employee onboarding systems: INSERT for adding new hires, similar to the Neha Sharma example
• Data sync pipelines: UPSERT logic to handle incoming data feeds that might contain either new or updated records, without needing separate insert/update branches in application code
• Payroll and HR systems: Conditional mass UPDATE for salary revisions, department reassignments, or policy-driven bulk changes, similar to the Engineering department salary hike
• Schema migrations: ALTER TABLE to add new tracking columns (like an audit flag or a new status field) as business requirements evolve over time
• Test environment resets: TRUNCATE TABLE to quickly reset a table back to empty before running a fresh batch of automated tests
• Data retention policies: DELETE with a WHERE clause to remove records older than a certain date, as part of scheduled cleanup jobs

Review Tips

Interviewers commonly test this topic alongside these ideas:

• "What's the difference between DELETE, TRUNCATE, and DROP?" — Tests understanding of the comparison table above, especially around WHERE support, speed, and whether the table itself survives.
• "Why is running UPDATE or DELETE without a WHERE clause considered dangerous?" — Tests awareness that these commands affect every row in the table when no condition is given.
• "What's the difference between CHANGE and MODIFY in an ALTER TABLE statement?" — Tests knowledge of MySQL-specific ALTER syntax, and when renaming is actually needed versus just changing a datatype.
• "How would you implement an UPSERT in a database that doesn't support ON DUPLICATE KEY UPDATE?" — Tests broader SQL knowledge across different database systems (like PostgreSQL's ON CONFLICT, or a manual check-then-insert-or-update approach).
• "What happens if an INSERT statement violates a table's constraints, like a CHECK constraint or a NOT NULL rule?" — Tests understanding that the entire operation is rejected, not partially applied.

A strong answer to any WHERE-related question should always mention that the safest habit is to write and mentally verify the WHERE clause (or run a SELECT with the same condition first, to preview which rows would be affected) before ever running an UPDATE or DELETE against real production data.

Common Mistakes to Avoid

Mistake 1: Forgetting the WHERE clause on UPDATE or DELETE

Why it happens: It's easy to write the SET or the row-removal logic first, and forget to add the WHERE clause before running the statement, especially when working quickly.

Wrong:

UPDATE employees
SET department = 'HR';

This updates every single row in the table, not just the one intended.

Correct: Always double check for a WHERE clause before executing UPDATE or DELETE against a real table, and consider running the equivalent SELECT statement with the same WHERE condition first, to confirm exactly which rows will be affected.

SELECT * FROM employees WHERE emp_id = 305; -- preview first
UPDATE employees SET department = 'HR' WHERE emp_id = 305; -- then update

Mistake 2: Relying on column order instead of naming columns explicitly in INSERT

Why it happens: It feels faster to skip listing out column names, especially for a table you already know well.

Wrong:

INSERT INTO employees VALUES (301, 'Neha Sharma', 'Sales', 55000.00, '2024-03-11', TRUE);

If the table's schema changes later (a column gets added or reordered), this exact same statement could silently insert values into the wrong columns.

Correct:

INSERT INTO employees (emp_id, name, department, salary, hire_date, is_active) 
VALUES (301, 'Neha Sharma', 'Sales', 55000.00, '2024-03-11', TRUE);

Mistake 3: Using DELETE without a WHERE clause when TRUNCATE would be more appropriate

Why it happens: DELETE is often the first command people learn, so it becomes the default choice even when clearing an entire table is the actual goal.

Less efficient:

DELETE FROM employees;

This works, but removes rows one at a time internally, which can be noticeably slower on large tables.

More efficient, when you genuinely want to remove everything:

TRUNCATE TABLE employees;

Just be aware that TRUNCATE typically cannot be filtered with a WHERE clause, and depending on the database, may behave differently around transactions and rollback compared to DELETE.

Mistake 4: Using CHANGE when MODIFY would have been safer

Why it happens: CHANGE can feel like the "more complete" option since it does more at once, but this also makes it riskier to use carelessly.

Risky (if renaming wasn't actually intended):

ALTER TABLE employees
CHANGE salary salary DECIMAL(12,2);

Even though the name is repeated here and technically "unchanged," it's easy to introduce a typo in one of the two name fields by accident, silently renaming the column when that wasn't the goal at all.

Safer, when only the datatype needs to change:

ALTER TABLE employees
MODIFY salary DECIMAL(12,2);

Mistake 5: Confusing an empty string with NULL when inserting optional columns

Why it happens: Both can look "blank" when quickly scanning through inserted data.

Why it matters: Inserting '' (an empty string) for department means "this employee does have a department value, it's just empty," while inserting NULL means "no department was recorded at all." These carry different meanings for later analysis or filtering, exactly like the NULL-vs-empty-string distinction from the CASE Expression notes. Mixing these up inconsistently across rows can make later queries (like counting "employees without a department") behave unpredictably, depending on whether you check for NULL, empty string, or both.

Mistake 6: Forgetting that DROP COLUMN permanently destroys data with no undo

Why it happens: ALTER statements can feel like routine schema housekeeping, making it easy to underestimate how destructive DROP COLUMN actually is.

Why it matters: Unlike an UPDATE that overwrites a value (which could theoretically be restored from a backup or audit log), DROP COLUMN removes the entire column and every value it held, across every row, permanently. Always be certain a column is no longer needed anywhere, in queries, in application code, or in reports, before dropping it in a production environment.

Key Takeaways

• INSERT adds new rows; always specify column names explicitly rather than relying on the table's default column order
• UPSERT (ON DUPLICATE KEY UPDATE in MySQL) inserts a new row, or updates it automatically if a matching primary key or unique constraint already exists
• UPDATE without a WHERE clause modifies every row in the table, this is one of the most dangerous and common mistakes in real production systems
• UPDATE with WHERE safely restricts changes to exactly the rows that should be affected, and multiple columns can be updated together in one statement
• DELETE without a WHERE clause removes every row, but the table structure itself survives; DELETE with WHERE or IN can target specific rows precisely
• ALTER TABLE changes a table's structure (adding, dropping, or renaming columns, or renaming the whole table) without touching existing data unless a column is explicitly dropped
• CHANGE renames a column and requires redefining its datatype, even if unchanged; MODIFY only changes a column's datatype or constraints while keeping its name the same, MODIFY is generally the safer default unless renaming is genuinely needed
• TRUNCATE instantly clears every row from a table, much faster than a DELETE without WHERE, but it cannot be filtered and the speed comes from working at a storage level rather than row by row
• DELETE, TRUNCATE, and DROP all sound similar but do fundamentally different things: DELETE removes rows (optionally filtered), TRUNCATE removes all rows instantly, and DROP removes the entire table, structure and all