- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - 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_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [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 −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
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 −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 35 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
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_name SET 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 = 24 WHERE ID = 3;
Verification
Using the SELECT query, we can retrieve the CUSTOMERS table with following records −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 35 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 24 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
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: 0
Verification
Using the SELECT query, we display the CUSTOMERS table with following records −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 24 | Ahmedabad | 2000.00 |
2 | Khilan | 24 | Delhi | 1500.00 |
3 | Kaushik | 24 | Kota | 2000.00 |
4 | Chaitali | 24 | Mumbai | 6500.00 |
5 | Hardik | 24 | Bhopal | 8500.00 |
6 | Komal | 24 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
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
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);
To update a view in a MySQL Database through a JavaScript program, we need to execute the UPDATE or ALTER statement using the query() function of mysql2 library as follows −
sql = "UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh'"; con.query(sql);
To update a view in a MySQL Database through a Java program, we need to execute the UPDATE or ALTER statement using the JDBC function executeUpdate() as follows −
String sql = "ALTER VIEW first_view AS select tutorial_id, tutorial_title, tutorial_author from tutorials_tbl where tutorial_id = 1"; st.execute(sql);
To update a view in a MySQL Database through a python program, we need to execute the UPDATE or ALTER statement using the execute() function of the MySQL Connector/Python as follows −
update_view_query = "UPDATE tutorial_view SET tutorial_title = 'New Title' WHERE tutorial_id = 2" cursorObj.execute(update_view_query)
Example
Following are the programs −
$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!.
var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); sql = "create database TUTORIALS" con.query(sql); sql = "USE TUTORIALS" con.query(sql); sql = "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) );" con.query(sql); sql = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh',32, 'Ahmedabad', 2000.00 ),(2, 'Khilan',25, 'Delhi', 1500.00 ),(3, 'kaushik',23, 'Kota', 2000.00),(4,'Chaitali', 25, 'Mumbai', 6500.00 ),(5, 'Hardik',27, 'Bhopal', 8500.00 ),(6, 'Komal',22, 'MP', 4500.00 ),(7, 'Muffy',24, 'Indore', 10000.00 );" con.query(sql); sql = "CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;" con.query(sql); //Displaying contents of the view sql = "SELECT * from CUSTOMERS_VIEW;" con.query(sql, function(err, result){ if (err) throw err console.log("**CUSTOMERS_VIEW View:**"); console.log(result); console.log("--------------------------"); }); sql = "UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh'"; con.query(sql); //retrieving the base table sql = "SELECT * FROM CUSTOMERS;" con.query(sql, function(err, result){ if (err) throw err console.log("**Base Table(CUSTOMERS):**"); console.log(result); }); });
Output
The output produced is as follows −
Connected! -------------------------- **CUSTOMERS_VIEW View:** [ {ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: 'Ahmedabad', SALARY: '2000.00'}, {ID: 2, NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: '1500.00'}, {ID: 3, NAME: 'kaushik', AGE: 23, ADDRESS: 'Kota', SALARY: '2000.00'}, {ID: 4, NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00'}, {ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'}, {ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' }, {ID: 7, NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00'} ] -------------------------- **Base Table(CUSTOMERS):** [ {ID: 1, NAME: 'Ramesh', AGE: 35, ADDRESS: 'Ahmedabad', SALARY: '2000.00'}, {ID: 2, NAME: 'Khilan', AGE: 25, ADDRESS: 'Delhi', SALARY: '1500.00'}, {ID: 3, NAME: 'kaushik', AGE: 23, ADDRESS: 'Kota', SALARY: '2000.00'}, {ID: 4, NAME: 'Chaitali', AGE: 25, ADDRESS: 'Mumbai', SALARY: '6500.00'}, {ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: 'Bhopal', SALARY: '8500.00'}, {ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: 'MP', SALARY: '4500.00' }, {ID: 7, NAME: 'Muffy', AGE: 24, ADDRESS: 'Indore', SALARY: '10000.00'} ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class UpdateView { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Update Created View..... String sql = "ALTER VIEW first_view AS select tutorial_id, tutorial_title, tutorial_author from tutorials_tbl where tutorial_id = 1"; statement.execute(sql); System.out.println("Created view updated Successfully...!"); ResultSet resultSet = statement.executeQuery("SELECT * FROM first_view"); while (resultSet.next()) { System.out.print(resultSet.getInt(1)+" "+ resultSet.getString(2)+ " "+ resultSet.getString(3)); System.out.println(); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! Created view updated Successfully...! 1 Learn PHP John Paul
import mysql.connector #establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) cursorObj = connection.cursor() update_view_query = """ UPDATE tutorial_view SET tutorial_title = 'New Title' WHERE tutorial_id = 2 """ cursorObj.execute(update_view_query) connection.commit() print("View updated successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
View updated successfully.
To Continue Learning Please Login
Login with Google