Course
Update Views
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
Update View
The MySQL UPDATE statement is used on various database objects to update the existing data in them. This is a DML (Data Manipulation language) command.
We need to be careful while using the UPDATE statement as it can modify all the records in an object, if not selected beforehand. To avoid losing or re-inserting correct data, we use clauses to filter the records that need to be updated. This way, we can update either a single row or multiple rows selectively.
MySQL UPDATE View Statement
In MySQL, a view is a database object that can contain rows (all or selected) from an existing table. It can be created from one or many tables which depends on the provided SQL query to create a view.
There is no direct statement to update a MySQL view. We use the UPDATE statement to modify all or selective records in a view. The results are reflected back in the original table as well.
Syntax
The basic syntax of the UPDATE query with a WHERE clause is as follows
UPDATE view_nameSET column1 = value1, column2 = value2...., columnN = valueNWHERE [condition]; Note: We can combine N number of conditions using the AND or the OR operators.
Example
First of all, let us create a table with the name CUSTOMERS using the following query
CREATE TABLE CUSTOMERS( ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS varchar(25), SALARY decimal(18, 2), PRIMARY KEY (ID));Now, we are inserting some records into this table using the INSERT statement as follows
INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000),(2, 'Khilan', '25', 'Delhi', 1500),(3, 'Kaushik', '23', 'Kota', 2500),(4, 'Chaitali', '26', 'Mumbai', 6500),(5, 'Hardik','27', 'Bhopal', 8500),(6, 'Komal', '22', 'MP', 9000),(7, 'Muffy', '24', 'Indore', 5500);Creating a view
Following query creates a view based on the above created table
CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;Using the following query, we can verify the contents of a view
SELECT * FROM CUSTOMERS_VIEW;The view will be displayed as follows
Updating this view
Now, through the view we created, we are trying to update the age of Ramesh to 35 in the original CUSTOMERS table, using the following query
UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh';This will ultimately update the base table CUSTOMERS and the same would reflect in the view itself.
Verification
Using a SELECT query, we can retrieve the actual CUSTOMERS table containing following records
Updating Multiple Rows and Columns
In MySQL, we can update multiple rows and columns of a table using the UPDATE statement. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it.
To update multiple columns, set the new values to all the columns that need to be updated. In this case, using the WHERE clause would narrow down the records of the table and not using the clause would change all the values in these columns.
Syntax
Following is the syntax to update multiple rows and columns
UPDATE table_nameSET column_name1 = new_value, column_name2 = new_value...WHERE condition(s)Example
In the following query, we are trying to modify the NAME and AGE column values in the CUSTOMERS table for WHERE ID = 3:
UPDATE CUSTOMERS_VIEW SET NAME = 'Kaushik', AGE = 24WHERE ID = 3;Verification
Using the SELECT query, we can retrieve the CUSTOMERS table with following records
Example
If we want to modify all the records of AGE column in the CUSTOMERS table, we can use the following query
UPDATE CUSTOMERS_VIEW SET AGE = 24;Output
This query produces the following output
Query OK, 5 rows affected (0.01 sec)Rows matched: 7 Changed: 5 Warnings: 0Verification
Using the SELECT query, we display the CUSTOMERS table with following records
Updated a View Using a Client Program
We have learned how to update a view using the SQL UPDATE query. In addition to it, we can also perform the update operation on a view using another client program.
Syntax
PHPNodeJSJavaPython
To update a view in a MySQL Database through a PHP program, we need to execute the UPDATE or ALTER statement using the mysqli function named query() as follows
$sql = "ALTER VIEW first_view AS SELECT tutorial_id, tutorial_title, tutorial_author FROM clone_table WHERE tutorial_id = 101";$mysqli->query($sql);Example
Following are the programs
PHPNodeJSJavaPython
$dbhost = 'localhost';$dbuser = 'root';$dbpass = 'password';$dbname = 'TUTORIALS';$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) { printf("Connect failed: %s", $mysqli->connect_error); exit();}// printf('Connected successfully.');
// A view can be updated by using the CREATE OR REPLACE;$sql = "ALTER VIEW first_view AS SELECT tutorial_id, tutorial_title, tutorial_author FROM clone_table WHERE tutorial_id = 101";if ($mysqli->query($sql)) { printf("View updated successfully!.");}if ($mysqli->errno) { printf("View could not be updated!.", $mysqli->error);} $mysqli->close(); Output
The output obtained is as follows
View updated successfully!.