MySQL - Update Rows in Table

MySQL – Update Rows in Table

The UPDATE statement in MySQL is used to modify existing rows in a table. This tutorial will cover the steps to create a database, insert sample data, and demonstrate various ways to update rows in a MySQL table.

Step 1: Create a Database

To start, we need a database to store our table. Use the CREATE DATABASE command to create a new database.

</>

Copy

CREATE DATABASE school;

This command creates a database named school. You can change the name if needed.

Step 2: Select the Database

After creating the database, use the USE command to select it so we can create tables and work with it.

</>

Copy

USE school;

Step 3: Create a Table

Now, let’s create a students table with columns for storing student details.

</>

Copy

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    rollno INT,
    age INT,
    class VARCHAR(10)
);

This command creates a students table with columns for ID, name, roll number, age, and class.

Step 4: Insert Data into the Table

Next, we insert some sample data into the students table to use in our SELECT examples.

</>

Copy

INSERT INTO students (name, rollno, age, class) VALUES ('Arjun', 14, 20, '10A');
INSERT INTO students (name, rollno, age, class) VALUES ('Raja', 15, 21, '10B');
INSERT INTO students (name, rollno, age, class) VALUES ('Prasanth', 16, 22, '10A');
INSERT INTO students (name, rollno, age, class) VALUES ('Hussain', 17, 23, '10C');
INSERT INTO students (name, rollno, age, class) VALUES ('Sai', 18, 20, '10B');
INSERT INTO students (name, rollno, age, class) VALUES ('Pranathi', 19, 21, '10A');

These commands insert six rows of data into the students table with details for each student.

Step 5: Update Rows in the Table

With data in the table, we can now use the UPDATE statement to modify specific rows. Here are various ways to update rows in MySQL:

Example 1: Update a Single Row

To update the age of a student with rollno 14, use the following query:

</>

Copy

UPDATE students
SET age = 21
WHERE rollno = 14;

This command updates the age of the student with roll number 14 to 21.

MySQL - Update a Single Row - Updated table

If safe mode is ON, you would get an error as shown in the following.

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Refer How to disable safe update mode.

Example 2: Update Multiple Columns in a Row

To update multiple columns at once, such as the age and class of a student named “Raja”, use this query:

</>

Copy

UPDATE students
SET age = 22, class = '11A'
WHERE name = 'Raja';

This command updates age to 22 and class to ’11A’ for the student named “Raja”.

MySQL - Update Multiple Columns in a Row

Example 3: Update Multiple Rows

To update multiple rows that meet a specific condition, such as updating the class for all students aged 20 to '10C', use this query:

</>

Copy

UPDATE students
SET class = '10C'
WHERE age = 20;

This command updates the class to ’10C’ for all students whose age is 20.

Example 4: Update All Rows (Use with Caution)

If you need to update all rows in a table, you can omit the WHERE clause. However, this will affect every row, so use it with caution. For example, to set a default value of ‘TBD’ in the class column for all students:

</>

Copy

UPDATE students
SET class = 'TBD';

This query updates the class of all students to ‘TBD’. Be careful, as this affects every row in the table.

Conclusion

The UPDATE statement in MySQL allows you to modify existing rows in a table based on specified conditions. Using the WHERE clause carefully is crucial to ensure only the intended rows are updated. With these examples, you now have a foundational understanding of how to use UPDATE to manage data in MySQL tables effectively.

TutorialKart

MySQL – Update Rows in Table – Step by Step Examples