Transform Data with the UPDATE Statement! 🔧

The UPDATE statement in SQL is your go-to for sprucing up existing records in a table! 🛠️ Whether you’re tweaking one column or many, it lets you change values based on specific conditions. Pair it with a WHERE clause to pinpoint rows, or combine it with subqueries, joins, or CTEs (Common Table Expressions) for ninja-level data tweaks. 🥷 Be cautious—without a WHERE clause, you might overhaul the entire table! In transactional databases, UPDATE can be rolled back if the transaction isn’t committed, giving you a safety net. 🛡️

Here’s the vibe:

• Basic Syntax 📝: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
• Flexibility 🎯: Update specific rows or use advanced techniques like joins for complex changes.
• Safety First ⚠️: Always double-check your WHERE clause to avoid accidental data makeovers.

Get ready to transform your data like a pro! 🌟

Sample Code: UPDATE in Action! 💻

Here’s a sample SQL query using an employees table (columns: employee_id, name, salary, department, last_updated) to showcase the UPDATE statement’s magic:

-- Create a sample employees table
CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(30),
    last_updated DATE
);

-- Insert sample data
INSERT INTO employees VALUES
(1, 'Alice Smith', 60000.00, 'HR', '2025-01-01'),
(2, 'Bob Jones', 75000.00, 'IT', '2025-02-01'),
(3, 'Clara Brown', 65000.00, 'HR', '2025-03-01'),
(4, 'David Lee', 80000.00, 'IT', '2025-04-01');

-- UPDATE: Give a raise to IT employees
UPDATE employees
SET salary = salary * 1.10, last_updated = '2025-10-18'  -- 10% raise and update timestamp
WHERE department = 'IT';

-- UPDATE: Complex update with a subquery
UPDATE employees
SET salary = (
    SELECT AVG(salary) * 1.05  -- Set to 5% above average salary
    FROM employees
    WHERE department = 'HR'
)
WHERE employee_id = 1;

-- Verify the changes
SELECT * FROM employees;