MySQL - Update View

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)

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.