Editing Data and Tables Problems

Safety note: These full-table data changes are educational examples. Run them on disposable data, or only after confirming a transaction and verified backup plan. Preview affected rows before changing important data.

Problem 1: System Settings

Problem Statement

A company stores its application configuration settings in a database table. Each setting can be marked as active or inactive. Due to a system-wide policy change, the company needs to deactivate every single configuration setting, regardless of what its current status is.

Business context Sometimes a company-wide decision (like a security policy change, or a planned maintenance freeze) requires turning off every setting at once, as a clean starting point, rather than selectively disabling just some of them.

Goal Update the table so that every single setting is marked as inactive.

Input A table listing every system setting along with its current active/inactive status.

Output The same table, but with every row's is_active value changed to 0 (inactive).

Table Structure

Table: SystemSettings

Column Meaning

• setting_id — a unique identifier for each configuration setting, and the primary key of this table
• setting_name — the human-readable name of the setting (like "EmailNotifications" or "AutoBackup")
• is_active — a flag indicating whether the setting is currently turned on (1) or off (0)

Important Notes

• setting_id is the primary key, so it's guaranteed to be unique.
• is_active is stored as a tinyint, which is commonly used in MySQL to represent boolean-style values, where 1 typically means true/active and 0 means false/inactive.
• The task explicitly says "regardless of their current status," meaning this update needs to affect every row in the table, including ones that are already inactive (setting them to inactive again has no visible effect, but the query still needs to run against all of them, not skip any).
• This is a clear, intentional case where omitting the WHERE clause is correct, not a mistake, since the policy genuinely applies company-wide, to every setting without exception.

Example

Input (Before Update)

Output (After Update)

CREATE TABLE SystemSettings (setting_id INTEGER, setting_name TEXT, is_active INTEGER);
INSERT INTO SystemSettings VALUES
  (1, 'EmailNotifications', 1),
  (2, 'AutoBackup', 1),
  (3, 'DarkMode', 0);

Explanation: Since no WHERE clause is used, all rows are updated. Settings 1 and 2, which were active, are now switched off. Setting 3, which was already inactive, simply stays inactive, its value doesn't change, but it's still technically included in the update.

Intuition

Think of this like a building manager doing a company-wide power shutdown before a long weekend. It doesn't matter which lights were already off and which were on, every single light switch in the building gets flipped to "off," without checking each room individually first.

This is one of the rare, genuine cases where updating a table with no WHERE clause is the correct approach, not a mistake, because the business rule truly says "every setting, no exceptions." The key skill being tested here isn't cleverness, it's recognizing when a broad, unfiltered UPDATE is actually the right call, versus when it would be a dangerous accident.

Mental Model

Picture a master switch panel with several individual toggles, each representing one setting. Normally, you'd flip toggles one at a time, carefully choosing which ones to change. But this task asks you to grab the panel with both hands and push every single toggle down to "off" at once, without looking at which ones were already down.

That's exactly what an UPDATE with no WHERE clause does, it applies the same change to every row, without checking each one's current value first.

Thought Process

Before writing SQL, ask the key clarifying question this problem is testing: does this change apply to every row, or only some of them?

1. Re-read the requirement: "deactivate all configuration settings, regardless of their current status." The phrase "regardless of current status" is the clue that no filtering condition is needed at all.
2. Since every row needs the same new value for is_active, this is a simple UPDATE with a SET clause.
3. Since there's no condition to filter by, there's no WHERE clause needed, and that's intentional here, not an oversight.

Approach

Step 1: Identify the table and column to change The SystemSettings table's is_active column needs to be updated.

Step 2: Set every row's is_active to 0 Use a plain SET is_active = 0.

Step 3: Deliberately omit the WHERE clause Since the rule applies to every row without exception, no WHERE clause is added, this is a conscious choice, not a mistake.

SQL Query

-- No WHERE clause is used on purpose: every setting must be deactivated, 
-- regardless of whether it was already active or inactive
UPDATE SystemSettings
SET is_active = 0;

Query Explanation

• UPDATE SystemSettings — targets the SystemSettings table for modification.
• SET is_active = 0 — sets every row's is_active column to 0, meaning inactive.
• No WHERE clause — this means the SET operation applies to every single row in the table, with no exceptions, which is exactly the intended behavior for this specific business rule.

Dry Run

Original table

↓

What SQL does: apply SET is_active = 0 to every row, with no filtering

↓

Final table

SQL Execution Flow

UPDATE SystemSettings
   ↓
Read every row in the table (no WHERE filter to narrow this down)
   ↓
Apply SET is_active = 0 to each row
   ↓
Commit the changes to every row

Since there's no WHERE clause, the database doesn't need to evaluate any condition per row, it simply applies the SET instruction uniformly across the entire table.

Time Complexity

Time Complexity: O(n) Every row in the table needs to be visited and updated, so the work grows directly with the number of rows in SystemSettings.

Space Complexity: O(1) extra space, beyond the table itself No sorting, grouping, or temporary structures are needed, this is a direct, uniform write operation across all existing rows.

Why: Without a WHERE clause to evaluate, the database doesn't need to do any conditional checking per row, it just needs to touch every row exactly once to apply the new value.

Common Mistakes to Avoid

Mistake 1: Adding an unnecessary WHERE clause out of habit

Why it happens: After being taught repeatedly that WHERE is important for safety, it can feel wrong to skip it entirely.

Unnecessary and potentially incorrect:

UPDATE SystemSettings
SET is_active = 0
WHERE is_active = 1;

This looks safer, but it's actually solving a slightly different problem: only deactivating settings that were previously active, when the requirement specifically says "regardless of current status." Functionally, the end result would look the same in this specific example (since the inactive row stays inactive either way), but conceptually, this WHERE clause narrows the rule to something more specific than what was actually asked.

Correct: Recognize when the business rule genuinely applies to every row without exception, and don't add a WHERE clause that wasn't actually needed, even if it happens to produce the same visible output in this particular case.

Mistake 2: Assuming "regardless of status" means you need a CASE expression

Why it happens: After working through many CASE-based problems, it can become an automatic reflex to reach for CASE whenever a condition is mentioned, even a condition that's actually being explicitly ignored.

Unnecessary:

UPDATE SystemSettings
SET is_active = CASE WHEN is_active = 1 THEN 0 ELSE 0 END;

Both branches return 0 here, since the rule is just "always set to 0", which makes the entire CASE expression pointless, it always evaluates to the same value no matter what.

Correct: A plain SET is_active = 0 already achieves exactly the same result, with far less complexity.

Mistake 3: Forgetting that this UPDATE has no undo

Why it happens: It's easy to run an UPDATE and move on, without pausing to think about whether the original is_active values are needed anywhere else later.

Why it matters: Once this UPDATE runs, the original information about which settings were active before this policy change is gone, unless it was backed up or logged somewhere first. If there's ever a need to "restore to how things were before," this needs to be planned for in advance, since a straightforward UPDATE like this one doesn't keep any history of the old values on its own.

Interview Follow-up Questions

• "How would you modify this so the original is_active values are preserved somewhere before being overwritten?"
• "What would happen if this same UPDATE statement was run twice in a row?"
• "How would you write this same query if the requirement was instead 'deactivate everything except settings named DarkMode'?"
• "Why might a team choose to log or audit an UPDATE like this one before running it in production?"

Similar Problems

• Mass status-reset problems, similar to resetting all user sessions, clearing all pending flags, or archiving an entire batch of records at once
• Problems that specifically test recognizing when NOT using a WHERE clause is the correct, intentional choice

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Business rule explicitly says "all," "every," or "regardless of current status" → a plain UPDATE with SET and no WHERE clause is likely intentional and correct
• Tempted to add a WHERE clause "just to be safe" → re-read the requirement carefully first, since adding an unnecessary condition can silently narrow the rule to something different than what was actually asked

Problem 2: Employee Salary

Problem Statement

A company stores employee details in an Employees table. After a performance review, the HR team has decided to give a salary hike, but only to employees working in one specific department.

Business context Department-wide raises are common after budget reviews or performance cycles. Unlike Problem 1, where every row needed to change, this scenario requires carefully targeting only a specific subset of employees, everyone else's salary must remain completely untouched.

Goal Increase the salary of every employee in the Engineering department by 10,000. No other department should be affected.

Input A table listing employees, their department, and their current salary.

Output The same table, with Engineering employees' salaries increased by 10,000, and every other employee's salary unchanged.

Table Structure

Table: Employees

Column Meaning

• employee_id — a unique identifier for each employee, and the primary key of this table
• name — the employee's name
• department — which department the employee currently belongs to
• salary — the employee's current salary

Important Notes

• employee_id is the primary key, so it's guaranteed to be unique.
• The salary increase applies only to employees whose department is exactly 'Engineering', every other department (HR, Finance, and so on) must remain completely unaffected.
• The increase is a fixed amount (10,000) added to whatever the employee's current salary already is, not a percentage, and not a fixed new salary value applied to everyone equally.
• Department name matching is likely case-sensitive or case-insensitive depending on the database's collation settings, similar to the collation discussion in the String Functions notes, so it's worth being precise about exact casing in a real system ('Engineering' vs 'engineering').

Example

Input: Employees table

Output (After Update)

CREATE TABLE Employees (employee_id INTEGER, name TEXT, department TEXT, salary INTEGER);
INSERT INTO Employees VALUES
  (1, 'Aarav', 'Engineering', 70000),
  (2, 'Neha', 'HR', 50000),
  (3, 'Rohan', 'Engineering', 80000),
  (4, 'Meera', 'Finance', 65000);

Explanation: Aarav and Rohan, both in Engineering, each received a 10,000 raise (70000 → 80000, and 80000 → 90000). Neha (HR) and Meera (Finance) were completely untouched, their salaries remain exactly as they were before the update.

Intuition

Think about an HR manager going through a spreadsheet with a highlighter, marking only the rows where the department column says "Engineering." Every row that gets highlighted gets its salary bumped up by a fixed amount. Every row that isn't highlighted stays exactly the same, completely untouched.

This is the natural counterpart to Problem 1. Where Problem 1 tested recognizing when no filter was needed, this problem tests the opposite and far more common scenario: making sure a targeted, filtered UPDATE only touches the rows it's supposed to, and nothing else.

Mental Model

Imagine a classroom where the teacher wants to give bonus points, but only to students sitting in one specific row of desks (representing the Engineering department). The teacher doesn't walk past every single student in the room adding bonus points, they specifically walk only to the desks in that one row, and add points only there. Every other student, sitting in different rows, simply doesn't get touched at all.

The WHERE clause is what tells the teacher exactly which "row of desks" to walk to, without it, they'd end up handing out bonus points to the entire classroom by mistake, exactly as happened in the dangerous "UPDATE without WHERE" example from the concept notes.

Thought Process

Before writing SQL, break the requirement into its two core pieces:

1. What needs to change? Salary, specifically increased by a fixed amount (10,000), based on each employee's own existing salary, not a flat new number.
2. Who does this apply to? Only employees where department = 'Engineering', nobody else.

Since the increase is relative to each person's own existing salary, the correct expression is salary = salary + 10000, not setting salary to one single fixed number for everyone in Engineering, since Aarav and Rohan clearly end up with different final salaries (80000 and 90000) despite receiving the same 10,000 increase.

Approach

Step 1: Identify the table and column to change The Employees table's salary column needs to be updated.

Step 2: Calculate the new salary relative to the existing value Use salary = salary + 10000, so the increase is added on top of whatever each employee already earns, rather than overwriting it with a single flat number.

Step 3: Restrict the update using WHERE Add WHERE department = 'Engineering' so only employees in that specific department are affected, everyone else remains completely untouched.

SQL Query

UPDATE Employees
-- Add a flat 10,000 increase on top of each employee's existing salary
SET salary = salary + 10000
-- Restrict the update strictly to the Engineering department
WHERE department = 'Engineering';

Query Explanation

• UPDATE Employees — targets the Employees table for modification.
• SET salary = salary + 10000 — this is a relative update, it reads each row's current salary value and adds 10,000 on top of it, meaning different employees can end up with different final salaries, depending on what they started with.
• WHERE department = 'Engineering' — this is the filtering condition that restricts the update to only rows where the department column exactly matches 'Engineering'. Every row that doesn't match this condition is left completely untouched, its salary value never even gets evaluated for this SET clause.

Dry Run

Original table

↓

Step 1: Evaluate the WHERE condition for every row

↓

Step 2: Apply salary = salary + 10000 only to matching rows

↓

Final table

SQL Execution Flow

UPDATE Employees
   ↓
Read each row, one at a time
   ↓
Evaluate WHERE department = 'Engineering' for that row
   ↓
If TRUE: apply SET salary = salary + 10000 to this row
If FALSE: leave this row completely untouched
   ↓
Commit the changes to only the matching rows

Unlike Problem 1, this UPDATE genuinely needs to check a condition for every row before deciding whether to apply the SET clause at all.

Time Complexity

Time Complexity: O(n), or faster with an index on department Every row generally needs to be checked against the WHERE condition, so the base cost scales with the number of rows. If the department column is indexed, the database might be able to jump directly to matching rows instead of scanning the entire table, which would make this faster in practice.

Space Complexity: O(1) extra space, beyond the table itself No sorting, grouping, or temporary structures are needed, each row is evaluated and updated (or skipped) independently as the table is scanned.

Why: This requires a per-row decision (does this row match the WHERE condition or not), unlike Problem 1's blanket update, so there is inherently some conditional checking happening for every row, even for rows that ultimately don't get changed.

Common Mistakes to Avoid

Mistake 1: Forgetting the WHERE clause entirely

Why it happens: After seeing Problem 1's intentional no-WHERE update, it's easy to mistakenly apply that same pattern here, forgetting that this specific problem explicitly requires filtering.

Wrong:

UPDATE Employees
SET salary = salary + 10000;

This would incorrectly give every single employee a raise, including HR and Finance employees who were never supposed to be affected at all.

Correct:

UPDATE Employees
SET salary = salary + 10000
WHERE department = 'Engineering';

Mistake 2: Setting salary to a flat number instead of adding to the existing value

Why it happens: "Increase by 10,000" can sometimes be misread as "set to 10,000 more than some baseline," rather than "add 10,000 to whatever this specific person already earns."

Wrong:

UPDATE Employees
SET salary = 80000
WHERE department = 'Engineering';

This would incorrectly set every Engineering employee's salary to exactly 80000, regardless of what they earned before, meaning Aarav (who should end up at 80000) would happen to look correct, but Rohan (who should end up at 90000, since he started at 80000) would be wrongly reduced from 80000 down to... well, actually stay the same in this coincidental case, but for employees at any other original salary, this approach would clearly produce the wrong final numbers.

Correct:

UPDATE Employees
SET salary = salary + 10000
WHERE department = 'Engineering';

Mistake 3: Using a case-sensitive or slightly mismatched department name

Why it happens: Department names might be stored inconsistently across a real dataset (like 'engineering', 'ENGINEERING', or 'Engineering ' with a trailing space), especially if the data came from multiple sources.

Why it matters: A condition like WHERE department = 'Engineering' might silently miss rows where the department was stored with different casing or extra whitespace, depending on the database's collation settings, similar to the collation and TRIM discussions from earlier chapters.

Correct: If there's any doubt about data consistency, consider using WHERE TRIM(department) = 'Engineering' or a case-insensitive comparison, or better yet, clean and standardize department names as a separate step before running sensitive updates like salary changes.

Mistake 4: Running the UPDATE multiple times by accident

Why it happens: If a script or query gets re-run accidentally (like resubmitting a form, or re-executing a saved query by mistake), a relative update like salary = salary + 10000 will apply the increase again each time it runs, unlike an UPDATE that sets an absolute fixed value.

Why it matters: Two accidental runs would give Engineering employees a 20,000 raise instead of 10,000, silently compounding the mistake, since there's nothing in the query itself that prevents it from running more than once.

Correct: Be especially careful with relative updates like this in production environments, consider wrapping sensitive updates in a transaction, verifying row counts affected after running the query, or maintaining a changelog for salary adjustments, so an accidental double-run can be caught and corrected quickly.

Interview Follow-up Questions

• "How would you modify this to give a percentage-based raise instead of a fixed amount?"
• "How would you write this if the raise needed to apply to multiple departments at once, like Engineering and Finance, but with different raise amounts for each?"
• "How would you verify, before running this UPDATE, exactly how many rows will be affected?"
• "What would happen if the department column contained NULL for some employees? Would they be included or excluded by this WHERE clause?"
• "How would you safely test this UPDATE in a way that lets you undo it if something goes wrong?"

Similar Problems

• Problem 3 from Day 1 (Special Bonus Calculation), which similarly combined a targeted condition with a calculated reward, though that problem used CASE to calculate a new column rather than UPDATE to modify existing data
• Any department-specific, role-specific, or tier-specific mass adjustment problem (like applying a discount to a specific customer segment, or adjusting inventory counts for a specific product category)
• Problems requiring a relative update (adding or multiplying an existing value) versus an absolute update (setting a fixed new value), similar to the "Conditional Mass UPDATE" example in the concept notes, which multiplied salary by 1.5 for recently hired employees

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Requirement says "increase by," "add," or "give a raise of" a specific amount → use a relative update (column = column + value), not a flat assignment
• Requirement applies to only a specific group, department, category, or segment → always include a WHERE clause matching that specific condition, never leave it out
• Requirement mentions a fixed amount added to an existing value (not a fixed final value for everyone) → double check that your SET expression references the column itself, not just a hardcoded number
• Text-based filtering condition involves department names, categories, or any user-entered text → consider whether casing or whitespace inconsistencies could cause rows to be silently missed