Category: sql

SQL – Rename View Table of content Renaming a View in MySQL Renaming a View in SQL Server Rules to be followed while Renaming Views ”; Previous Next There are various SQL statements that perform different operations on database objects, such as creating, updating, deleting and also renaming a database object. And since a view is also a database object, all these operations can also be performed on a view, you can create a view, update a view, delete a view and also rename a view. There is no direct query to rename a view in SQL. In MySQL we can rename a view using the RENAME TABLE statement and in MS SQL Server we can rename a view using the sp_rename procedure. In many cases, deleting the existing view and then re-creating it with a new name is rather recommended. Renaming a View in MySQL The RENAME TABLE statement in MySQL database is used to rename views. You just have to make sure that the new name of the view does not overlap with the name of any existing views. Syntax Following is the basic syntax to rename a view in MySQL − RENAME TABLE old_view_name To new_view_name; Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Also before executing this statement, it is important to make sure that the table is not locked and there are no active transactions. Example In this example, let us first create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. As shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created 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 Following query creates a view based on the above created table − CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS WHERE AGE > 25; You can verify the contents of a view using the select query as shown below − SELECT * from CUSTOMERS_VIEW; The view is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 Now we know that a view with the name CUSTOMERS_VIEW exists in our database. So, we are directly going to rename this view to VIEW_CUSTOMERS, using the following query − RENAME TABLE CUSTOMERS_VIEW TO VIEW_CUSTOMERS; Output The result obtained is as shown below − Query OK, 0 rows affected (0.08 sec) Verification We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view − SELECT * from VIEW_CUSTOMERS; The view displayed is as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 Renaming a View in SQL Server There isn”t a query in SQL Server that can rename a view directly. But, it does give you access to a stored procedure called sp_rename that can rename a view. You have to make sure there are no active transactions being performed on the view using its old name before renaming it. The sp_rename is a system stored procedure (set of pre-built subroutines that perform tasks within the database) in SQL that can be used to rename various database objects including tables, columns, indexes, and constraints. Syntax Following is the basic syntax to rename a view in SQL − EXEC sp_rename ”old_view_name”, ”new_view_name” Here, we must ensure that old view name is present in the database and that new view name does not already exist. Otherwise, it will issue a warning. Before executing this statement, it is important to make sure that the table is not locked and there are no active transactions. Example In this example, let us first try to create a table with the name CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. As shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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 ); Following query creates a view based on the above created table − CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS WHERE SALARY >2000; You can verify the contents of a view using the select query as shown below − SELECT * from CUSTOMERS_VIEW; The view will be created as − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 Now, we know that we have an existing view CUSTOMERS_VIEW in our database. So, we are going to rename this view to VIEW_CUSTOMERS, using the following query − EXEC sp_rename CUSTOMERS_VIEW, VIEW_CUSTOMERS; Verification We can verify whether the view is renamed or not by retrieving its contents using its new name in the SELECT statement. Following is the query to display the records in the VIEW_CUSTOMERS view − SELECT * FROM VIEW_CUSTOMERS; The view displayed is

SQL – EXISTS Operator Table of content The SQL EXISTS Operator EXISTS Operator with SELECT Statement EXISTS Operator with UPDATE Statement EXISTS Operator with DELETE Statement NOT Operator with EXISTS Operator Usage of SQL EXISTS Operator ”; Previous Next The SQL EXISTS Operator The SQL EXISTS operator is used to verify whether a particular record exists in a MySQL table. While using this operator we need to specify the record (for which you have to check the existence) using a subquery. The EXISTS operator is used in the WHERE clause of a SELECT statement to filter records based on the existence of related records in another table. It is a logical operator. It returns a Boolean value TRUE or FALSE. It returns TRUE if the subquery returns at least one record. If the EXISTS operator returns TRUE, the outer query will get executed; otherwise not. It can be used in SELECT, UPDATE, DELETE or INSERT statements. The EXISTS operator is more efficient than other operators, such as IN, because it only needs to determine whether any rows are returned by the subquery, rather than actually returning the data. The use of the EXISTS operator is an efficient way to filter data in many real-life scenarios, including filtering records based on the existence of related data, aggregating data based on the existence of related records, and optimizing queries. Syntax The basic syntax of the SQL EXISTS operator is as follows − WHERE EXISTS (subquery); Where, the subquery used is the SELECT statement. The EXISTS operator will evaluate to TRUE if the subquery returns at least one record in its result set; otherwise FALSE. EXISTS Operator with SELECT Statement The SELECT statement in SQL is used to retrieve data from one or more tables in a database. We can use the EXISTS operator with a SELECT statement to check for the existence of rows that meet a certain condition. Example To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created 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 Let us create another table CARS, containing the details such as id of the customer, name and price of the car, using the following query − create table CARS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, PRICE INT NOT NULL, PRIMARY KEY(ID) ); Using the INSERT statement, let us insert values into this table − insert INTO CARS VALUES (2, ”Maruti Swift”, 450000), (4, ”VOLVO”, 2250000), (7, ”Toyota”, 2400000); The ”CARS” table obtained is as follows − ID NAME PRICE 2 Maruti Swift 450000 4 VOLVO 2250000 7 Toyota 2400000 Now, we are retrieving the lists of the customers with the price of the car greater than 2,000,000 − SELECT * FROM CUSTOMERS WHERE EXISTS ( SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000 ); Output The result produced is as follows − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 7 Muffy 24 Indore 10000.00 EXISTS Operator with UPDATE Statement We can also use the SQL EXISTS operator with an UPDATE statement. It helps us to update rows in a table based on the existence of matching rows in another table. Example Suppose if we want to change the name of certain customers from the CUSTOMERS and CARS tables previously created, then this can be done using UPDATE statement. Here, we are modifying the name ”Kushal” of all the customers whose id is equal to the id of the CARS table, using the EXISTS operator, as follows − UPDATE CUSTOMERS SET NAME = ”Kushal” WHERE EXISTS ( SELECT NAME FROM CARS WHERE CUSTOMERS.ID = CARS.ID ); Output We get the following result. We can observe that 3 rows have been modified − Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMERS table − SELECT * FROM CUSTOMERS; The table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Kushal 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Kushal 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Kushal 24 Indore 10000.00 As we can see in the above table, the NAME of ”Khilan”, ”Chaitali” and ”Muffy” has been updated to ”Kushal”. EXISTS Operator with DELETE Statement The EXISTS operator can also be used with a DELETE statement to delete rows based on the existence of rows returned by a subquery. Example In here, we are deleting the row in the CUSTOMERS table whose id is equal to the id of the CARS table having price equal to ”2250000” − DELETE FROM CUSTOMERS WHERE EXISTS ( SELECT * FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND CARS.PRICE = 2250000 ); Output We get the following result. We can observe that 1 row has been deleted − Query OK, 1 row affected (0.01 sec) Verification We can rectify the changes done in the CUSTOMERS table using the following query −

SQL – WHERE Clause Table of content The SQL Where Clause WHERE Clause with SELECT Statement WHERE Clause with UPDATE Statement WHERE Clause with IN Operator WHERE Clause with NOT IN Operator WHERE Clause with LIKE Operator WHERE Clause with AND, OR Operators ”; Previous Next The SQL Where Clause The SQL WHERE clause is used to filter the results obtained by the DML statements such as SELECT, UPDATE and DELETE etc. We can retrieve the data from a single table or multiple tables(after join operation) using the WHERE clause. For instance, you can use the WHERE clause to retrieve details of employees of a department in an organization, or employees earning salary above/below certain amount, or details of students eligible for scholarships etc. This clause basically provides the specification of which records to be retrieved and which are to be to be neglected. Syntax The basic syntax of the SQL WHERE clause is as shown below − DML_Statement column1, column2,… columnN FROM table_name WHERE [condition]; Here, the DML_Statement can be any statement, such as SELECT, UPDATE, DELETE etc. You can specify a condition using the comparison or logical operators such as, >, <, =, LIKE, NOT, etc. WHERE Clause with SELECT Statement Typically, the SELECT statement is used to retrieve data from a table. If we use the WHERE clause with the SELECT statement, we can filter the rows to be retrieved based on a specific condition (or expression). Following is the syntax for it − SELECT column1, column2, … FROM table_name WHERE condition; Example Assume we have created a table named CUSTOMERS in MySQL database using CREATE TABLE statement as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Following INSERT query inserts 7 records into this table − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table created is as shown below − 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 In the following query, we are fetching the ID, NAME and SALARY fields from the CUSTOMERS table for the records where the SALARY is greater than 2000 − SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000; Output This would produce the following result − ID NAME SALARY 4 Chaitali 6500.00 5 Hardik 8500.00 6 Komal 4500.00 7 Muffy 10000.00 WHERE Clause with UPDATE Statement The UPDATE statement is used to modify the existing records in a table. Using the SQL WHERE clause with the UPDATE statement, we can update particular records. If the WHERE clause is not used, the UPDATE statement would affect all the records of a table. Following is the syntax − UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; Example In the following query, we are incrementing the salary of the customer named Ramesh by 10000 by using the WHERE clause along with the UPDATE statement − UPDATE CUSTOMERS set SALARY = SALARY+10000 where NAME = ”Ramesh”; Output We get the following result. We can observe that the age of 2 customers have been modified − Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0 Verification To verify if the changes are reflected in the table, we can use SELECT statement as shown in the following query − SELECT * FROM CUSTOMERS WHERE NAME = ”Ramesh”; The table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 12000.00 WHERE Clause with IN Operator Using the IN operator you can specify the list of values or sub query in the where clause. If you use WHERE and IN with the SELECT statement, it allows us to retrieve the rows in a table that match any of the values in the specified list. Following is the syntax for it − WHERE column_name IN (value1, value2, …); Where, the column_name is the column of a table and value1, value2, etc. are the list of values that we want to compare with the column_name. Example Suppose you want to display records with NAME values Khilan, Hardik and Muffy from the CUSTOMERS table, you can use the following query − SELECT * from CUSTOMERS WHERE NAME IN (”Khilan”, ”Hardik”, ”Muffy”); Output The result obtained is as follows − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 WHERE Clause with NOT IN Operator The WHERE clause with NOT IN operator is the negation of WHERE clause with the IN operator. If you use WHERE with the IN operator, the DML statement will act on the the list of values (of a column) specified Whereas, if you use WHERE with the NOT IN operator, the DML operation is performed on the values (of a column) that are not there in the specified list. Hence, if you use WHERE Clause with NOT IN Operator along with the SELECT statement, the rows that do not match the list of values are retrieved. Following is the syntax − WHERE column_name NOT IN (value1, value2, …); Example In this example, we are displaying the records from CUSTOMERS table, where AGE is NOT equal to 25, 23 and 22. SELECT * from CUSTOMERS WHERE AGE NOT IN (25, 23, 22); Output We obtain the result as given below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 12000.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 WHERE Clause with LIKE Operator The WHERE clause with LIKE operator allows us to filter rows that matches

SQL – CREATE View Table of content What is SQL View The SQL CREATE VIEW Statement Create View With WHERE Clause The WITH CHECK OPTION Clause ”; Previous Next What is SQL View A view in SQL is a virtual table that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query. A view can contain rows from an existing table (all or selected). A view can be created from one or many tables. Unless indexed, a view does not exist in a database. The data in the view does not exist in the database physically. A view is typically created by the database administrator and is used to − Structure data in a way that users or classes of users find natural or intuitive. Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more. Summarize data from various tables which can be used to generate reports. The SQL CREATE VIEW Statement To create a view in a database, you can use the SQL CREATE VIEW statement. Syntax Following is the syntax of the SQL CREATE VIEW statement − CREATE VIEW view_name AS SELECT column1, column2…. FROM table_name WHERE [condition]; Example Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Following query creates a view based on the above created table − CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS; Verification You can verify the contents of a view using the select query as shown below − SELECT * FROM CUSTOMERS_VIEW; The view is 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 Create View With WHERE Clause We can also create a view with only specific records from a table using the where clause along with the SQL CREATE VIEW statement as shown below − CREATE VIEW BUYERS_VIEW as SELECT * FROM CUSTOMERS WHERE SALARY > 3000; Verification Following are the contents of the above created view − SELECT * FROM BUYERS_VIEW; The view is displayed as follows − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The WITH CHECK OPTION Clause The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) specified by the WHERE clause. If they do not satisfy the condition(s), the UPDATE or INSERT statements return an error. The following example creates the view named BUYERS_VIEW with the WITH CHECK OPTION clause. CREATE VIEW MY_VIEW AS SELECT name, age FROM CUSTOMERS WHERE age >= 25 WITH CHECK OPTION; The WITH CHECK OPTION in this case should deny the entry and updates of the of records whose age value is greater than or equal to 25. Verification Following are the contents of the above created view − SELECT * FROM MY_VIEW; The view is displayed as follows − NAME AGE Ramesh 32 Khilan 25 Chaitali 25 Hardik 27 Print Page Previous Next Advertisements ”;

SQL – IN Operator Table of content The SQL IN Operator IN Operator with SELECT Statement IN Operator with UPDATE Statement IN Operator with NOT IN Operator with Column Name Subquery with IN Operator ”; Previous Next The SQL IN Operator The SQL IN Operator is used to specify multiple values or sub query in the WHERE clause. It returns all rows in which the specified column matches one of the values in the list. The list of values or sub query must be specified in the parenthesis e.g. IN (select query) or IN (Value1, Value2, Value3, …). In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. Alternatively, we can use the IN operator instead of multiples OR statements. The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values. The IN operator is useful when you want to select all rows that match one of a specific set of values. While the OR operator is useful when you want to select all rows that match any one of multiple conditions. Syntax The basic syntax of the SQL IN operator to specify multiple values is as follows − WHERE column_name IN (value1, value2, value3, …); Where, value1, value2, value3, … are the values in the list to be tested against the expression. The IN operator returns TRUE if any of these values is found in the list, and FALSE if it is not. IN Operator with SELECT Statement We can use the SQL IN operator to specify multiple values in a WHERE clause, and we can also use it in a SELECT statement to retrieve data that matches any of the specified values. Here, we are using the IN operator to specify multiple values in SELECT statement. Example In this example, we are using the IN operator to specify multiple values in SELECT statement consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table will be created 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 Suppose based on the above table we want to display records with NAME equal to ”Khilan”, ”Hardik” and ”Muffy”(string values). This can be achieved using IN operator as follows − SELECT * FROM CUSTOMERS WHERE NAME IN (”Khilan”, ”Hardik”, ”Muffy”); Output The result obtained is as follows − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 Example The above query can also be done using OR operator. Following is an example − SELECT * FROM CUSTOMERS WHERE NAME = ”Khilan” OR NAME = ”Hardik” OR NAME = ”Muffy”; Output ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 IN Operator with UPDATE Statement We can also use the SQL IN operator in an UPDATE statement to update rows that match any of the specified values in a WHERE clause. The UPDATE statement is used to modify existing data in a database table. Example Here, we are using the IN operator to specify multiple values in the UPDATE statement and updating the CUSTOMERS table previously created. Here, we are changing the records of the customers with age ”25” or ”27” and updating the age value to ”30” − UPDATE CUSTOMERS SET AGE = 30 WHERE AGE IN (25, 27); Output We get the following result. We can observe that the age of 3 customers has been modified − Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 Verification We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMERS table − SELECT * FROM CUSTOMERS; The table is displayed as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 30 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 30 Mumbai 6500.00 5 Hardik 30 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 As we can see in the above table, the AGE of ”Khilan”, ”Chaitali” and ”Hardik” has been updated to ”30”. IN Operator with NOT To negate a condition, we use the NOT operator. The SQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause. In other words, the absence of a list from an expression will be checked. Syntax Following is the basic syntax of NOT IN operator − WHERE column_name NOT IN (value1, value2, …); Example Now, we are displaying all the records from the CUSTOMERS table, where the AGE is NOT equal to ”25”, ”23” and ”22” − SELECT * FROM CUSTOMERS WHERE AGE NOT IN (25, 23, 22); Output We obtain the result as given below − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27 Bhopal 8500.00 7 Muffy 24 Indore 10000.00 IN Operator with Column Name We can also use the SQL IN operator with a column name to compare the values of one column to another. It

SQL – Like Operator Table of content The SQL LIKE Operator What are wild cards? The ”%” Wildcard character The ”_” wildcard character LIKE operator with OR NOT operator with the LIKE condition Escape characters with LIKE operator Uses of LIKE Operator in SQL ”; Previous Next The SQL LIKE Operator The SQL LIKE operator is used to retrieve the data in a column of a table, based on a specified pattern. It is used along with the WHERE clause of the UPDATE, DELETE and SELECT statements, to filter the rows based on the given pattern. These patterns are specified using Wildcards. Suppose we need to submit the list of all the students whose name starts with ”K”. We can obtain this with the help of the LIKE operator as follows − WHERE student_name LIKE ”K%”; Here, the % is a wild card which represents zero, one or multiple characters. And the expression K% specifies that it will display the list of all the students whose name starts with ”k”. The LIKE operator can be used with strings, numbers, or date values. However, using the string values is recommended. Syntax The basic syntax of the SQL LIKE operator is as follows − SELECT column1, column2, … FROM table_name WHERE columnn LIKE specified_pattern; What are wild cards? SQL wildcards are special characters used in SQL queries to match patterns in the data. Following are the wildcards used in conjunction with the LIKE operator in MySQL database − S.No WildCard & Definition 1 % The percent sign represents zero, one or multiple characters. 2 _ The underscore represents a single number or character. In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The table given below has a few examples showing the WHERE clause having different LIKE operators with ”%” and ”_” − S.No Statement & Description 1 WHERE SALARY LIKE ”200%” Finds any values that start with 200. 2 WHERE SALARY LIKE ”%200%” Finds any values that have 200 in any position. 3 WHERE SALARY LIKE ”_00%” Finds any values that have 00 in the second and third positions. 4 WHERE SALARY LIKE ”2_%_%” Finds any values that start with 2 and are at least 3 characters in length. 5 WHERE SALARY LIKE ”%2” Finds any values that end with 2. 6 WHERE SALARY LIKE ”_2%3” Finds any values that have a 2 in the second position and end with a 3. 7 WHERE SALARY LIKE ”2___3” Finds any values in a five-digit number that start with 2 and end with 3. The ”%” Wildcard character The % sign represents zero or multiple characters. The ”%” wildcard matches any length of a string which even includes the zero length. Example To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created 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 Now, let us display all the records from the CUSTOMERS table, where the SALARY starts with 200 − SELECT * FROM CUSTOMERS WHERE SALARY LIKE ”200%”; Output This would produce the following result − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 3 Kaushik 23 Kota 2000.00 Example Below is the query that displays all the records from the CUSTOMERS table previously created with the NAME that has ”al” in any position. Here, we are using multiple ”%” wildcards in the LIKE condition − SELECT * FROM CUSTOMERS WHERE NAME LIKE ”%al%”; Output The following result is produced − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 6 Komal 22 Hyderabad 4500.00 The ”_” wildcard character The underscore wild card represents a single number or character. A single ”_” looks for exactly one character similar to the ”%” wildcard. Example Following is the query which would display all the records from the CUSTOMERS table previously created, where the Name starts with K and is at least 4 characters in length − SELECT * FROM CUSTOMERS WHERE NAME LIKE ”K___%”; Output The result obtained is given below − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 6 Komal 22 Hyderabad 4500.00 Example Following is the query to display all the records from the CUSTOMERS table, where the NAME has ”m” in the third position − SELECT * FROM CUSTOMERS WHERE NAME LIKE ”__m%”; Output We get the following result on executing the above query − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 6 Komal 22 Hyderabad 4500.00 LIKE operator with OR We can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators. Syntax Following is the basic syntax of using LIKE operator with OR operator − SELECT column1, column2, … FROM table_name WHERE column1 LIKE pattern1 OR column2 LIKE pattern2 OR …; Example Here, the SQL query retrieves the records of the customers whose name starts with C and ends with i, or customers whose name ends with k − SELECT * FROM CUSTOMERS WHERE NAME LIKE ”C%i” OR NAME LIKE ”%k”; Output This will produce the following result

SQL – Insert Into… Select Statement Table of content The Insert Into… Select Statement SQL – Inserting Specific Records SQL – Inserting Top N Rows ”; Previous Next The Insert Into… Select Statement The SQL INSERT INTO… SELECT statement is used to add/insert one or more new rows from an existing table to another table. This statement is a combination of two different statements: INSERT INTO and SELECT. The INSERT INTO statement is one of the most fundamental and frequently used statements in database management and requires only the name of the table and the values to be inserted. However, it is important to ensure that the data being inserted satisfies the constraints if the columns of a table (if any) and its type matches the data types of the table columns. The SELECT statement is used to retrieve data from an existing database table. When these statements are used together, the SELECT statement first retrieves the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures). Syntax Following is the syntax of the SQL INSERT INTO… SELECT statement − INSERT INTO table_new SELECT (column1, column2, …columnN) FROM table_old; Before using this query, we have to make sure that − In the database where we are going to insert data, source and target tables already exist. The structure of the source and target tables are same. Example Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc.., as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The table will be created as − 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 Create another table named BUYERS with same structure as the CUSTOMERS table. CREATE TABLE BUYERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Following query copies all the records from the CUSTOMERS table to BUYERS − INSERT INTO BUYERS SELECT * FROM CUSTOMERS; Verification If you verify the contents of the BUYERS table using the SELECT statement as − SELECT * FROM BUYERS; The table will be created as − 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 SQL – Inserting Specific Records Sometimes we only need to add a small number of records to another table. This can be accomplished by using a WHERE clause along with the SQL INSERT INTO… SELECT statement. Example Let us create a table named NAMESTARTSWITH_K with the same structure as the CUSTOMER table using the CREATE statement as − CREATE TABLE NAMESTARTSWITH_K ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Following query inserts the records of the customers whose name starts with the letter k from the CUSTOMERS table to the BUYERS table − INSERT INTO NAMESTARTSWITH_K SELECT * FROM CUSTOMERS WHERE NAME LIKE ”k%”; Verification Following is the SELECT statement to verify the contents of the above created table − SELECT * FROM NAMESTARTSWITH_K; The table will be created as − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 6 Komal 22 Hyderabad 4500.00 SQL – Inserting Top N Rows The LIMIT clause filters the number of rows from the query. You can use this to filter the top N records that should be added to the target table. Example But, before proceeding further, let us truncate all rows in the BUYERS table using the following statement − TRUNCATE TABLE BUYERS; Following query inserts the top 3 records from the CUSTOMERS table to the BUYERS table − INSERT INTO BUYERS SELECT * FROM CUSTOMERS ORDER BY ID ASC LIMIT 3; Verification Let us verify the contents of the BUYERS table − SELECT * FROM BUYERS; The resultant table will be 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 Print Page Previous Next Advertisements ”;

SQL – DELETE Query Table of content The SQL DELETE Statement Deleting Multiple Rows Deleting All The Records From a Table Delete Records in Multiple Tables ”; Previous Next The SQL DELETE Statement The SQL DELETE Statement is used to delete the records from an existing table. In order to filter the records to be deleted (or, delete particular records), we need to use the WHERE clause along with the DELETE statement. If you execute DELETE statement without a WHERE clause, it will delete all the records from the table. Using the DELETE statement, we can delete one or more rows of a single table and records across multiple tables. Syntax The basic syntax of the SQL DELETE Query with the WHERE clause is as follows − DELETE FROM table_name WHERE [condition]; You can combine N number of conditions using AND or OR operators. Example Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created as − 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 The following query deletes the record of a customer, whose ID is 6. DELETE FROM CUSTOMERS WHERE ID = 6; Output The output will be displayed as − Query OK, 1 row affected (0.10 sec) Verification To verify whether the record(s) have been deleted from the table, we need to retrieve the modified table using the SELECT query, as shown below − SELECT * FROM CUSTOMERS; Now, the CUSTOMERS table would have the following records − 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 7 Muffy 24 Indore 10000.00 Deleting Multiple Rows To delete multiple rows from a table, we need to specify the required condition(s), that is satisfied by all the rows to be deleted, using the WHERE clause. Let us look at an example − Example From the same Customers table, let us try to delete the records of customers who are over 25 years of age. DELETE FROM CUSTOMERS WHERE AGE > 25; Output The output will be displayed as − Query OK, 2 rows affected (0.06 sec) Verification To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below − SELECT * FROM CUSTOMERS; The query above will produce the following table − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 7 Muffy 24 Indore 10000.00 Deleting All The Records From a Table If we want to DELETE all the records from an existing table (truncate it) using the DELETE query, we simply need to run it without using the WHERE clause. Example Following SQL query removes all the records from the CUSTOMERS table − DELETE FROM CUSTOMERS; Output The output will be displayed as − Query OK, 4 rows affected (0.13 sec) Verification To verify whether all the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query − SELECT * FROM CUSTOMERS; Now, the CUSTOMERS table would not have any record and will show the following output − Empty set (0.00 sec) Delete Records in Multiple Tables SQL allows us to delete the records from multiple tables using the DELETE query. In here, we will use the JOIN clause to combine data from multiple tables (based on a common column). Example Let us create another table with name ORDERS which contains the details of the orders made by the customers. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2) ); Using the INSERT statement, insert values into this table as follows INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table created is as shown below − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Following SQL query deletes the records of the customers (from the tables CUSTOMERS and ORDERS) who earn more than 2000 and have placed orders − DELETE CUSTOMERS, ORDERS FROM CUSTOMERS INNER JOIN ORDERS ON ORDERS.CUSTOMER_ID = CUSTOMERS.ID WHERE CUSTOMERS.SALARY > 2000; Output The output will be displayed as − Query OK, 2 rows affected (0.01 sec) Verification To verify whether the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query − SELECT * FROM CUSTOMERS; The CUSTOMERS table would not have the record(s) where salary is greater than 2000 and the ID matches with the CUSTOMER_ID column in the ORDERS table. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 MP 4500.00 7 Muffy 24 Indore 10000.00 Similarly, if you verify the ORDERS table as shown below − SELECT * FROM ORDERS; Since salary is greater

SQL – UPDATE Query Table of content The SQL UPDATE Statement Update Multiple ROWS and COLUMNS ”; Previous Next The SQL UPDATE Statement The SQL UPDATE Statement is used to modify the existing records in a table. This statement is a part of Data Manipulation Language (DML), as it only modifies the data present in a table without affecting the table”s structure. To filter records that needs to be modified, you can use a WHERE clause with UPDATE statement. Using a WHERE clause, you can either update a single row or multiple rows. Since it only interacts with the data of a table, the SQL UPDATE statement needs to used cautiously. If the rows to be modified aren”t selected properly, all the rows in the table will be affected and the correct table data is either lost or needs to be reinserted. The SQL UPDATE statement makes use of locks on each row while modifying them in a table, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query. Syntax The basic syntax of the SQL UPDATE statement with a WHERE clause is as follows − UPDATE table_name SET column1 = value1, column2 = value2,…, columnN = valueN WHERE [condition]; You can combine N number of conditions using the AND or the OR operators. Example Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); The table will be created as − 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 The following query will update the ADDRESS for a customer whose ID number is 6 in the table. UPDATE CUSTOMERS SET ADDRESS = ”Pune” WHERE ID = 6; Output The query produces the following output − Query OK, 1 row affected (0.13 sec) Rows matched: 1 Changed: 1 Warnings: 0 Verification To verify whether the records of the table are modified or not, use the following SELECT query below − SELECT * FROM CUSTOMERS WHERE ID=6; Now, the CUSTOMERS table would have the following records − ID NAME AGE ADDRESS SALARY 6 Komal 22 Pune 4500.00 Update Multiple ROWS and COLUMNS Using SQL UPDATE statement, multiple rows and columns in a table can also be updated. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it. However, 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 If you want to modify all the AGE and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough. Following query increases the age of all the customers by 5 years and adds 3000 to all the salary values − UPDATE CUSTOMERS SET AGE = AGE+5, SALARY = SALARY+3000; Output The query produces the following output − Query OK, 7 rows affected (0.12 sec) Rows matched: 7 Changed: 7 Warnings: 0 Verification To verify whether the records of the table are modified or not, use the following SELECT query below − SELECT * FROM CUSTOMERS; Now, CUSTOMERS table would have the following records − ID NAME AGE ADDRESS SALARY 1 Ramesh 37 Ahmedabad 5000.00 2 Khilan 30 Delhi 4500.00 3 Kaushik 28 Kota 5000.00 4 Chaitali 30 Mumbai 9500.00 5 Hardik 32 Bhopal 11500.00 6 Komal 27 Pune 7500.00 7 Muffy 29 Indore 13000.00 Example But, if you want to modify the ADDRESS and the SALARY columns of selected records in the CUSTOMERS table, you need to specify a condition to filter the records to be modified, using the WHERE clause, as shown in the following query − UPDATE CUSTOMERS SET ADDRESS = ”Pune”, SALARY = 1000.00 WHERE NAME = ”Ramesh”; Output This query produces the following output − Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 Verification To verify whether the records of the table are modified or not, use the following SELECT query below − SELECT * FROM CUSTOMERS WHERE NAME = ”Ramesh”; Now, CUSTOMERS table would have the following records − ID NAME AGE ADDRESS SALARY 1 Ramesh 37 Pune 1000.00 Print Page Previous Next Advertisements ”;

SQL – UPDATE View Table of content SQL UPDATE View Statement Updating Multiple Rows and Columns ”; Previous Next SQL UPDATE View Statement 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. Unlike CREATE VIEW and DROP VIEW there is no direct statement to update the records of an existing view. We can use the SQL UPDATE Statement to modify the existing records in a table or a view. 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]; You can combine N number of conditions using the AND or the OR operators. Example Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS 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, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Following query creates a view based on the above created table − CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS; You can verify the contents of a view using the SELECT query as shown below − 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 Following query updates the age of Ramesh to 35 in the above created CUSTOMERS_VIEW − UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = ”Ramesh”; Verification You can verify the contents of the CUSTOMERS_VIEW using the SELECT statement as follows − SELECT * FROM CUSTOMERS_VIEW WHERE NAME =”Ramesh”; The resultant view would have the following record(s) − ID NAME AGE ADDRESS SALARY 1 Ramesh 35 Ahmedabad 2000.00 Example The following query will update the ADDRESS of a customer whose ID is 6 in the CUSTOMERS_VIEW. UPDATE CUSTOMERS_VIEW SET ADDRESS = ”Pune” WHERE ID = 6; Output The query produces the following output − Query OK, 1 row affected (0.21 sec) Rows matched: 1 Changed: 1 Warnings: 0 Verification If you retrieve the record with ID value 6 using the SELECT statement as − SELECT * FROM CUSTOMERS_VIEW WHERE ID=6; The record returned would be − ID NAME AGE ADDRESS SALARY 6 Komal 22 Hyderabad 4500.00 Updating Multiple Rows and Columns Using UPDATE statement, multiple rows and columns in a view/table can also be updated. While updating multiple rows, specify the condition in a WHERE clause such that only required rows would satisfy it. Example Following query updates the NAME and AGE column values in the CUSTOMERS_VIEW of the record with ID value 3. UPDATE CUSTOMERS_VIEW SET NAME = ”Kaushik Ramanujan”, AGE = 24 WHERE ID = 3; Output The query produces the following output − Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 Verification You can verify whether the record is updated or not, using the following query − SELECT * FROM CUSTOMERS_VIEW WHERE ID = 3; The record returned would be − ID NAME AGE ADDRESS SALARY 3 Kaushik Ramanujan 24 Kota 2000.00 Example But if you want to modify/update the age values of all the records in the CUSTOMERS_VIEW, there is no need to use the WHERE clause. UPDATE CUSTOMERS_VIEW SET AGE = AGE+6; Output This query produces the following output − Query OK, 7 rows affected (0.10 sec) Rows matched: 7 Changed: 7 Warnings: 0 Verification To verify whether the records of the CUSTOMERS_VIEW are modified or not, use the following SELECT query − SELECT * FROM CUSTOMERS_VIEW; The resultant CUSTOMERS_VIEW would have the following records − ID NAME AGE 1 Ramesh 41 2 Khilan 31 3 Kaushik Ramanujan 30 4 Chaitali 31 5 Hardik 33 6 Komal 28 7 Muffy 30 Print Page Previous Next Advertisements ”;