UPDATE Data in SQL

Imagine this situation…

You open a production database and find:

• Dates stored as text
• Wrong spellings in state names
• Salary entered incorrectly
• Status column not updated

You cannot delete everything.

You must correct it.

That is where UPDATE comes in.

What is UPDATE in SQL?

UPDATE is used to modify existing records in a table.

Basic syntax:

UPDATE table_name
SET column_name = new_value
WHERE condition;

UPDATE table_name
SET column_name = new_value
WHERE condition;

Important:

If you do not use WHERE clause,

👉 It will update ALL rows.

That can be dangerous.

Read More: INSERT Data into Tables in SQL

Basic UPDATE Example

Suppose employee Amit got salary increment.

PostgreSQL

UPDATE employees
SET salary = 50000
WHERE employee_code = 'EMP001';

UPDATE employees
SET salary = 50000
WHERE employee_code = 'EMP001';

MySQL

Same syntax:

UPDATE employees
SET salary = 50000
WHERE employee_code = 'EMP001';

UPDATE employees
SET salary = 50000
WHERE employee_code = 'EMP001';

Simple and identical in both databases.

Update Multiple Columns

Let’s update both salary and department.

UPDATE employees
SET salary = 52000,
    department_id = 2
WHERE employee_code = 'EMP001';

UPDATE employees
SET salary = 52000,
    department_id = 2
WHERE employee_code = 'EMP001';

You can update multiple columns at once.

Update Based on Condition

Increase price of all beverages by 5.

UPDATE products
SET price = price + 5
WHERE category = 'Beverages';

UPDATE products
SET price = price + 5
WHERE category = 'Beverages';

This is called dynamic update.

We are not setting fixed value, we are modifying existing value.

Very common in real business scenarios.

Update Using Another Column

Suppose you want to update current_stock in inventory.

PostgreSQL:

UPDATE inventory i
SET current_stock = i.stock_in - i.stock_out;

UPDATE inventory i
SET current_stock = i.stock_in - i.stock_out;

MySQL:

Same syntax works.

Real Data Cleaning Example

Now let’s take your real-world type example.

Sometimes date column is stored as TEXT like:

15-Jan-2024 05:30 PM

Instead of proper DATETIME.

We need to convert it.

MySQL Version (Using STR_TO_DATE)

UPDATE incentive_data 
SET FinalDecisionDate = STR_TO_DATE(FinalDecisionDate, '%d-%b-%Y %h:%i %p') 
WHERE FinalDecisionDate IS NOT NULL;

UPDATE incentive_data 
SET FinalDecisionDate = STR_TO_DATE(FinalDecisionDate, '%d-%b-%Y %h:%i %p') 
WHERE FinalDecisionDate IS NOT NULL;

What is happening here?

• STR_TO_DATE() converts string to proper DATETIME
• Format ‘%d-%b-%Y %h:%i %p’ means:
  • %d → day
  • %b → short month name
  • %Y → year
  • %h → hour (12 format)
  • %i → minutes
  • %p → AM/PM

This is very common in messy Excel imports.

PostgreSQL Version (Using TO_TIMESTAMP())

PostgreSQL does not use STR_TO_DATE().

It uses TO_TIMESTAMP().

Equivalent query:

UPDATE incentive_data
SET FinalDecisionDate = TO_TIMESTAMP(FinalDecisionDate, 'DD-Mon-YYYY HH12:MI AM')
WHERE FinalDecisionDate IS NOT NULL;

UPDATE incentive_data
SET FinalDecisionDate = TO_TIMESTAMP(FinalDecisionDate, 'DD-Mon-YYYY HH12:MI AM')
WHERE FinalDecisionDate IS NOT NULL;

Important Differences:

This is a very important difference for Data Analysts.

Update NULL Values

Sometimes phone column is empty.

Set default value:

UPDATE customers
SET phone = 'Not Available'
WHERE phone IS NULL;

UPDATE customers
SET phone = 'Not Available'
WHERE phone IS NULL;

Works in both databases.

Update with CASE

Example:

Mark employees inactive if salary < 20000.

UPDATE employees
SET is_active = 
    CASE 
        WHEN salary < 20000 THEN FALSE
        ELSE TRUE
    END;

UPDATE employees
SET is_active = 
    CASE 
        WHEN salary < 20000 THEN FALSE
        ELSE TRUE
    END;

This works in PostgreSQL.

In MySQL:

Replace FALSE/TRUE with 0/1 if needed.

Dangerous Example (Never Do This)

UPDATE employees
SET salary = 0;

UPDATE employees
SET salary = 0;

If you forget WHERE condition:

All employee salaries become 0 😱

Always check with SELECT first:

SELECT * FROM employees
WHERE employee_code = 'EMP001';

SELECT * FROM employees
WHERE employee_code = 'EMP001';

Then run UPDATE.

Best Practices Before Running UPDATE

1. Always run SELECT with same WHERE condition
2. Use transactions in PostgreSQL
3. Take backup in production
4. Avoid updating primary keys
5. Be careful with date conversions

PostgreSQL Bonus: Use Transaction

BEGIN;

UPDATE employees
SET salary = 55000
WHERE employee_code = 'EMP001';

ROLLBACK;  -- undo changes

BEGIN;

UPDATE employees
SET salary = 55000
WHERE employee_code = 'EMP001';

ROLLBACK;  -- undo changes

If everything looks good:

Replace ROLLBACK with COMMIT.

Very powerful feature in PostgreSQL.

Why UPDATE is Very Important for Data Analysts

In real life you will:

• Correct wrong dates
• Fix incorrect salary formats
• Replace wrong state names
• Standardize text (RAIPUR → Raipur)
• Convert VARCHAR to proper date
• Clean imported Excel data

Most of your work will be data correction, not just SELECT queries.

That’s why mastering UPDATE is critical.

Quick Summary

UPDATE modifies existing data.

You can:

• Update single row
• Update multiple columns
• Update with condition
• Update using CASE
• Convert string to date
• Fix NULL values

PostgreSQL and MySQL syntax is mostly same.

Main difference:

• STR_TO_DATE() (MySQL)
• TO_TIMESTAMP() (PostgreSQL)

What’s Next?

Now we are ready for:

👉 ALTER TABLE (Modify Table Structure)