MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL UPDATE: Modify Data (Update Data)

The UPDATE statement in MySQL is used to modify the existing records in a table. You can update one or more columns at a time. Here's the syntax:

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

The SET clause specifies the column names and new values to replace the existing data. The WHERE clause specifies which records should be updated. If you omit the WHERE clause, all records in the table will be updated.

Example:

Let's say you have a table students with columns id, name, age, grade.

idnameagegrade
1John15B
2Jane14A
3Bill16C
4Mary15B

And you want to update the grade of the student 'John' from 'B' to 'A'. You would write:

UPDATE students
SET grade = 'A'
WHERE name = 'John';

After this query, the table will look like this:

idnameagegrade
1John15A
2Jane14A
3Bill16C
4Mary15B

Multiple Columns Update:

You can update multiple columns in a single UPDATE statement. Let's say you also want to update 'John's age to 16:

UPDATE students
SET grade = 'A', age = 16
WHERE name = 'John';

Update Using Expressions:

You can use expressions in the SET clause. For example, if you want to increase the age of all students by 1:

UPDATE students
SET age = age + 1;

Important: Be careful when using the UPDATE statement without the WHERE clause. It will update all rows in the table.

Update Using Subqueries:

Sometimes, you need the UPDATE statement to be based on the result of another SQL query. For example, you want to update the grade of all students whose age is greater than the average age:

UPDATE students
SET grade = 'A'
WHERE age > (SELECT AVG(age) FROM students);

Caution: Always be sure to use the UPDATE statement wisely, as it can change the data in your table. If you use the wrong WHERE clause, you could change data in the wrong way.

  1. MySQL UPDATE Statement Example:

    • Description: The UPDATE statement in MySQL is used to modify the existing records in a table.
    • Example Code:
      UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

  2. How to Use UPDATE in MySQL:

    • Description: Use UPDATE to modify data in a table based on specified conditions.
    • Example Code:
      UPDATE employees
SET salary = 60000
WHERE department = 'IT';

  3. Modifying Records in a Table with UPDATE in MySQL:

    • Description: The UPDATE statement allows you to change the values of existing records.
    • Example Code:
      UPDATE products
SET price = price * 1.1;

  4. Updating Specific Columns in MySQL:

    • Description: Specify the columns you want to update in the SET clause.
    • Example Code:
      UPDATE orders
SET status = 'Shipped', shipped_date = NOW()
WHERE order_id = 1001;

  5. Updating Multiple Records with WHERE in MySQL:

    • Description: Use the WHERE clause to update multiple records based on specified conditions.
    • Example Code:
      UPDATE customers
SET discount = discount * 1.05
WHERE total_purchases > 1000;

  6. Conditional Updates with CASE in MySQL UPDATE:

    • Description: Use the CASE statement to perform conditional updates based on different criteria.
    • Example Code:
      UPDATE products
SET stock_status = 
    CASE
        WHEN stock_quantity > 0 THEN 'In Stock'
        ELSE 'Out of Stock'
    END;

  7. Batch Updates in MySQL using UPDATE:

    • Description: UPDATE can be used to perform batch updates by specifying multiple conditions.
    • Example Code:
      UPDATE employees
SET bonus = bonus + 500
WHERE department = 'Sales' AND sales > 100000;

  8. Examples of Using UPDATE in MySQL Queries:

    • Description: Various examples showcasing the application of UPDATE in different scenarios.
    • Example Code:
      UPDATE student_grades
SET grade = 'A'
WHERE subject = 'Math' AND score >= 90;

      UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 101;
Copyright © devanddep.com | All Rights Reserved