Insert and modify records - INSERT, UPDATE and DELETE

Insert and modify records - INSERT, UPDATE and DELETE

Inserting records - INSERT INTO

The INSERT INTO statement is used to add new rows of data to a table in the database.

Syntax :

There are two ways to use the INSERT INTO statement, as shown below.

                                INSERT INTO table_name (column1, column2, column3,...columnN)  
                                VALUES (value1, value2, value2,...valueN);
                            

column1, column2, column3,...columnN are the names of the columns of the table in which you want to insert the data.

You may not need to specify the name of the column (s) in the SQL query if you are adding values for all of the columns in the table. But make sure that the order of the values is in the same order as that of the columns in the table.

                                INSERT INTO table_name
                                VALUES (value1, value2, value2,...valueN);
                            
Example 1 :

This statement will add a single record to the Employees table

                                INSERT INTO Employees (Id, Name, Age, Salary) 
                                VALUES(1, "Ismail", 25, 6000);
                            

Since we fill in all the columns, we can use INSERT INTO without specifying the columns.

                                INSERT INTO Employees VALUES(1, "Ismail", 25, 6000);
                            
+----+--------+-----+---------+
| Id | Name   | Age | Salary  |
+----+--------+-----+---------+
|  1 | Ismail |  25 | 6000.00 |
+----+--------+-----+---------+
1 row in set (0.00 sec)
                            

To insert multiple records into the table, use the following syntax

                                INSERT INTO table_name VALUES 
                                (value1, value2, value2,...valueN),
                                (value1, value2, value2,...valueN),
                                (value1, value2, value2,...valueN),
                                .....
                                (value1, value2, value2,...valueN);
                            
Example 1 :
                                INSERT INTO Employees VALUES
                                    (2, "Adam", 30, 8000.40),
                                    (3, "Fatima", 29, 6000);
                            
+----+---------+-----+---------+
| Id | Name    | Age | Salary  |
+----+---------+-----+---------+
|  1 | Ismail  |  25 | 6000.00 |
|  2 | Adam    |  30 | 8000.40 |
|  3 | Fatima  |  29 | 6000.00 |
+----+---------+-----+---------+
3 rows in set (0.00 sec)
                            

Editing records - UPDATE

The UPDATE query is used to modify existing records in a table. You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax:
                                UPDATE table_name
                                SET column1 = value1, column2 = value2...., columnN = valueN
                                [WHERE condition];
                            

You can combine a number of conditions using the AND or OR operators.

Exemple 1 :

Consider the Employees table with the following records

+----+---------+-----+---------+
| Id | Name    | Age | Salary  |
+----+---------+-----+---------+
|  1 | Ismail  |  25 | 6000.00 |
|  2 | Adam    |  30 | 8000.40 |
|  3 | Fatima  |  29 | 6000.00 |
|  4 | Dounia  |  31 | 9000.00 |
|  5 | Omar    |  30 | 7500.00 |
+----+---------+-----+---------+
5 rows in set (0.00 sec)
                            

The following query will update an employee's salary by adding 900 DH to his old salary.

                                UPDATE Employees
                                SET Salaire=Salaire+900;
                            

Now the Employees table would have the following records:

+----+---------+-----+---------+
| Id | Name    | Age | Salary  |
+----+---------+-----+---------+
|  1 | Ismail  |  25 | 6900.00 |
|  2 | Adam    |  30 | 8900.40 |
|  3 | Fatima  |  29 | 6900.00 |
|  4 | Dounia  |  31 | 9900.00 |
|  5 | Omar    |  30 | 8400.00 |
+----+---------+-----+---------+
5 rows in set (0.00 sec)
                            

The following query will update the salary of each employee whose age is greater than or equal to 30 years.

                                UPDATE Employees
                                SET Salaire=Salaire+500
                                WHERE age>=30;
                            

Now the Employees table would have the following records:

+----+---------+-----+----------+
| Id | Name    | Age | Salary   |
+----+---------+-----+----------+
|  1 | Ismail  |  25 | 6900.00  |
|  2 | Adam    |  30 | 9400.40  |
|  3 | Fatima  |  29 | 6900.00  |
|  4 | Dounia  |  31 | 10400.00 |
|  5 | Omar    |  30 | 8900.00  |
+----+---------+-----+----------+
5 rows in set (0.00 sec)
                            

Delete records - DELETE

The DELETE query is used to delete existing records from a table.

You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

Syntax :
                                DELETE FROM table_name
                                [WHERE condition];
                            
Example 1 :

The following query deletes an employee whose Id is 4.

                                DELETE FROM Employees WHERE Id=4;
                            

Now the Employees table would have the following records:

+----+---------+-----+----------+
| Id | Name    | Age | Salary   |
+----+---------+-----+----------+
|  1 | Ismail  |  25 | 6900.00  |
|  2 | Adam    |  30 | 9400.40  |
|  3 | Fatima  |  29 | 6900.00  |
|  5 | Omar    |  30 | 8900.00  |
+----+---------+-----+----------+
4 rows in set (0.00 sec)
                            

If you want to delete all the records from the Employees table, you do not need to use the WHERE clause.

                                DELETE FROM Employees;
                            

Now the Employees table would have the following records:

+----+---------+-----+----------+
| Id | Name    | Age | Salary   |
+----+---------+-----+----------+
+----+---------+-----+----------+
0 rows in set (0.00 sec)
                            

Share this course with your friends :

This course is written by M. ESSADDOUKI Mostafa

Many people realize their hearts desires late in life. Continue learning, never stop striving and keep your curiosity sharp, and you will never become too old to appreciate life.

0 Comment(s)

To leave a comment you must have an account Sign up, or Sign in