SQL UPDATE Row(s) in TABLE

SQL UPDATE Statement

The SQL UPDATE statement is used to modify existing records in a table. This statement allows you to update the values of specific columns for one or more rows in a table.

---

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

The UPDATE statement has the following components:

• table_name: The name of the table where the data will be updated.
• column1, column2, ...: The columns to be updated.
• value1, value2, ...: The new values for the specified columns.
• condition: The condition that specifies which rows to update. If omitted, all rows in the table will be updated.

---

Example SQL UPDATE Statement Queries

Let's look at some examples of SQL UPDATE statement queries:

1. Basic UPDATE Example

UPDATE employees
SET email = 'john.newemail@example.com'
WHERE id = 1;

This query updates the email address for the employee with id 1. The result will be that the email column for the specified employee is updated to 'john.newemail@example.com'.

2. UPDATE Multiple Columns

UPDATE employees
SET first_name = 'Jane', last_name = 'Doe'
WHERE id = 2;

This query updates both the first_name and last_name columns for the employee with id 2. The result will be that the specified columns are updated to 'Jane' and 'Doe', respectively.

3. UPDATE Multiple Rows

UPDATE employees
SET email = 'default@example.com'
WHERE email IS NULL;

This query updates the email column for all employees where the email is NULL. The result will be that the email column for these rows is updated to 'default@example.com'.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, updating data, and querying the table.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

In this example, we create a table named employees with columns for id, first_name, last_name, and email.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');

INSERT INTO employees (first_name, last_name, email)
VALUES ('Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO employees (first_name, last_name, email)
VALUES ('Jim', 'Brown', NULL);

Here, we insert data into the employees table.

Step 3: Updating Data in the Table

This step involves updating some data in the employees table.

UPDATE employees
SET email = 'jim.brown@example.com'
WHERE id = 3;

UPDATE employees
SET email = 'default@example.com'
WHERE email IS NULL;

Here, we update the email column for the employee with id 3 and set a default email for any employee with a NULL email.

Step 4: Querying the Table

This step involves selecting the data from the employees table to view the updated records.

SELECT * FROM employees;

This query retrieves all the rows from the employees table. The result will be:

id  first_name  last_name  email
--- ----------- ---------- ------------------------
1   John        Doe        john.doe@example.com
2   Jane        Smith      jane.smith@example.com
3   Jim         Brown      jim.brown@example.com

Conclusion

The SQL UPDATE statement is a powerful tool for modifying existing records in a table. Understanding how to use the UPDATE statement and its syntax is essential for effective data management and manipulation in SQL databases.