Insert Update Delete Data Rows using MySQL Workbench

Back to: MySQL Tutorials for Beginners and Professionals

Insert, Update, Delete Data Rows using MySQL Workbench

In this article, I am going to discuss How to Insert, Update, and Delete Records in a Table in MySQL Database using MySQL Workbench. Please read our previous article, where we discussed how to create, alter and drop database tables using MySQL Workbench.

Insert, Update, Delete Data Rows using MySQL Workbench

Now we will see how to insert, update and delete data rows using MySQL Workbench. For this, we need a table. So, let us first create a database called the school and then create a table called students within the school database. Then we will see how to insert, update, delete records from the Students table. So, please use the below SQL Script to create the school database and Students table.

CREATE DATABASE School;
USE School;

CREATE TABLE `School`.`Students`(
  StudentId INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  Class VARCHAR(50),
  Age INT
);

Once you execute the above scripts, then the School database and Students table will be created. Under the SCHEMAS menu, select the database name i.e. School, expand the tables folder, and you can see the table Students that we created as shown in the below image.

When we hover the mouse pointer, over the ‘Students’ table name, then you can see three icons appears. The first icon will give you information about the table. If you want to update the table schema like adding a new column(s), updating an existing column, changing data type, or deleting a column, then you need to click on the second icon. The third icon shows you the table data. Click on the third table icon as shown in the below image.

Once you click on the table icon i.e. the third icon, it which will open a new window, in which the upper section shows the MySQL statement, while the lower section shows the data rows present in the Students table. At this moment, we don’t have any data row in the Students table. So, the table shows NULL data row values as shown in the below image.

How to Insert Data Rows to MySQL Database table using MySQL Workbench?

To enter a new data row, just select the respected column and type the data value. It’s something similar to the Microsoft Excel spreadsheet. Enter a couple of data rows and click on the Apply button as shown in the below image. Here, I am going to insert three records.

Once you click on the Apply button, it will open the below Apply SQL Script to Database window. Simply verify the SQL Script and if everything fine, then click on the Apply button to save the data rows as shown in the below image.

Once you click on the Apply button, it will open the following popup, simply click on the click Finish button as shown in the below image.

We can also import the data rows from an external ‘CSV file with a similar table structure and data columns and that we will discuss in our next article.

How to Update Data Rows using MySQL Workbench?

We can edit the previously saved data row using MySQL Workbench. Let’s say we want to update the FirstName as Preety and Age as 16 of Student whose StudentId is 2. Then you can update the data directly here and then click on the Apply button as shown in the below image.

Once you click on the Apply button, it will open the following Apply SQL Script to Database window and if you further notice this time, it generates an SQL update statement and Click on the Apply button save the changes to the database as shown in the below image.

Once you click on the Apply button, it will open the below window, and here simply click on the Finish button as shown in the below image.

How to Delete Data Rows using MySQL Workbench?

To delete an individual row from the table, select the data row, right-click the ‘right icon’ in-front of the row and select the ‘delete row’ option. For example, if you want to delete the student whose id is 3, then select the third row and then right-click on it and click on the Delete Row(s) as shown in the below image.

This will delete the row immediately from the GUI. To delete the row from the database, click on the Apply button as shown in the below image.

Once you click on the Apply button, it will open the below window with the Delete statement. Click on the Apply button to save the changes into the database as shown in the below image.

Once you click on the Apply, it will open the below window, simply click on the Finish button.

As of now, we have seen how to create, update and delete rows using the GUI provided by MySQL Workbench. It is also possible in MySQL workbench, to create, update and delete data rows using raw SQL script.

Insert, Update, Delete using SQL Script in MySQL Workbench:

Let us see how to perform insert, update and delete operations using SQL Script in MySQL Workbench. First, open a new query tab and then execute the following SQL Script which will Insert two records into the Students table of the School database.

INSERT INTO `school`.`students` (StudentId, FirstName, LastName, Class, Age) 
VALUES (3, 'Hina', 'Sharma', 'First', 18);
INSERT INTO `school`.`students` (StudentId, FirstName, LastName, Class, Age) 
VALUES (4, 'Rakesh', 'Sahoo', 'Second', 17);

Now, we want to update the FirstName of the Student whose Id is 3 and LastName of the Student whose Id is 4. Then the following Update SQL Script will update the same in the Students table.

UPDATE `school`.`students` SET `FirstName` = 'Suresh' WHERE (`StudentId` = '3');
UPDATE `school`.`students` SET `LastName` = 'Mohanty' WHERE (`StudentId` = '4');

Now, we want to delete the student whose IDs are 3 and 4. Then the following DELETE SQL Script will delete the same from the Students table.

DELETE FROM `school`.`students` WHERE (`StudentId` = '3');
DELETE FROM `school`.`students` WHERE (`StudentId` = '4');

In the next article, I am going to discuss Database Export and Import using MySQL Workbench. Here, in this article, I try to explain How to Insert, Update, Delete Data Rows using MySQL Workbench and I hope you enjoy this Insert, Update, Delete Data Rows using MySQL Workbench article.