SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

SQL UPDATE

SQL UPDATE

The SQL UPDATE statement is utilized to update and modify the records present into a database. It is used to change the already existing records stored in the tables in the database. This command is worked along with WHERE clause. The condition specified in the UPDATE statement is used to decide which columns or rows of the table will be affected or modified.

The UPDATE command can also be used to update a table by another table. It can be used to update the date and time of a SQL query as well.

SQL UPDATE JOIN

The SQL UPDATE statement can also be utilized to update one table using another table which are connected by a join. This is known as the SQL UPDATE JOIN statement.

UPDATE table1, table2,
 INNER JOIN table1
 ON table1.column1 = table2.column1 
 SET table1.column1 = table2.column2
 WHERE condition

The following is the basic syntax of the UPDATE statement using the JOIN clause:

Example:

CREATE TABLE TAE1 (Col1 INT, Col2 INT, Col3 VARCHAR (100))  
 INSERT INTO TAE1 (Col1, Col2, Col3)  
 SELECT 1, 11, 'FIRST'  
 UNION ALL  
 SELECT 11,12, 'SECOND'  
 UNION ALL   
 SELECT 21, 13, 'THIRD'  
 UNION ALL   
 SELECT 31, 14, 'FOURTH'   

Let’s first create the first table TAE1.

 CREATE TABLE TAE2 (Col1 INT, Col2 INT, Col3 VARCHAR (100))  
 INSERT INTO TAE2 (Col1, Col2, Col3)  
 SELECT 1, 21, 'TWO-ONE'  
 UNION ALL  
 SELECT 11,22, 'TWO-TWO'  
 UNION ALL   
 SELECT 21, 23, 'TWO-THREE'  
 UNION ALL   
 SELECT 31, 24, 'TWO-FOUR'   

Now, let’s create the second table TAE2.

SELECT *
 FROM TAE1 
Col1Col2Col3
111First
1112Second
2113Third
3114Fourth

Output:

Now let’s see the contents of the table TAE2.

SELECT *
 FROM TAE2

Output:

Col1Col2Col3
121Two-one
1122Two-two
2123Two-three
3124Two-four

Now the following query will update the rows of TAE1 where the value of Col1 is 21 and 31 using the table TAE2 where there are similar rows and where Col1 is 21 and 31. Only the corresponding records of Col2 and Col3 of TAE1 table will be updated.

UPDATE TAE1
 SET Col2 = TAE2.Col2,  
 Col3 = TAE2.Col3  
 FROM TAE1  
 INNER JOIN TAE2 ON TAE1.Col1 = TAE2.Col1  
 WHERE TAE1.Col1 IN (21, 31); 

Output:

Col1Col2Col3
111First
1112Second
2113Two-three
3114Two-four

Now, if the contents of the table TAE1 is checked, the following output will be obtained.

However, the contents of TAE2 remains unchanged.

Col1Col2Col3
121Two-one
1122Two-two
2123Two-three
3124Two-four

This is an example of using the JOIN clause with the UPDATE statement. It has merged the above two tables.

SQL UPDATE DATE

The SQL UPDATE DATE statement is used to update the date and time field in SQL.

UPDATE table_name
 SET data_field = ‘data_value’ 
 WHERE conditions; 

The following is the general syntax of updating the date and time field in SQL:

UPDATE table_name
 SET data_field = getdate(); 

The following is the syntax of updating the date with the current date in SQL:

UPDATE table_name
 SET data_field = CURRENT_TIMESTAMP; 

The following is the syntax of updating the date and time with the current date and time in SQL:

 UPDATE table_name
 SET data_field = ‘YYYY-MM-DD HH:MM:SS’;

The following is the syntax of updating the date and time with a specific date and time in SQL:

UPDATE table_name
 SET data_field = CAST(‘date_value’ AS DATETIME); 

The following is the syntax of updating the date with a specific value when the format of the date is not known:

Example:

UPDATE Employee
 SET DOJ = ‘2021-07-05’ 
 WHERE Dept_ID = 10; 

The following is an example of updating the date in multiple rows in the given Employee table.

Emp_IDEmp_NameDesignationManager_IDDOJSalaryDept_ID
1Emp1Director 2021-07-114500010
2Emp2Director 2021-07-114000020
3Emp3ManagerEmp12021-07-112700010
4Emp4ManagerEmp22021-10-082500020
5Emp5AnalystEmp32021-07-112000010
6Emp6AnalystEmp32021-10-081800010
7Emp7ClerkEmp32021-07-111500010
8Emp8SalesmanEmp42021-09-091400020
9Emp9SalesmanEmp42021-10-081300020

Query:

Output:

Emp_IDEmp_NameDesignationManager_IDDOJSalaryDept_ID
1Emp1Director 2021-07-054500010
3Emp3ManagerEmp12021-07-052700010
5Emp5AnalystEmp32021-07-052000010
6Emp6AnalystEmp32021-07-051800010
7Emp7ClerkEmp32021-07-051500010

Thus, the DOJ column for all the employees having Dept_ID 10 has been updated.

The following is the example of updating the admission date in a student table using the CAST function:

UPDATE student
SET admission_date = CAST(‘2021-04-10’ AS DATETIME)
WHERE id = 42;



ADVERTISEMENT
ADVERTISEMENT