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 

Col1	Col2	Col3
1	11	First
11	12	Second
21	13	Third
31	14	Fourth

Output:

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

SELECT *
 FROM TAE2

Output:

Col1	Col2	Col3
1	21	Two-one
11	22	Two-two
21	23	Two-three
31	24	Two-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:

Col1	Col2	Col3
1	11	First
11	12	Second
21	13	Two-three
31	14	Two-four

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

However, the contents of TAE2 remains unchanged.

Col1	Col2	Col3
1	21	Two-one
11	22	Two-two
21	23	Two-three
31	24	Two-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_ID	Emp_Name	Designation	Manager_ID	DOJ	Salary	Dept_ID
1	Emp1	Director		2021-07-11	45000	10
2	Emp2	Director		2021-07-11	40000	20
3	Emp3	Manager	Emp1	2021-07-11	27000	10
4	Emp4	Manager	Emp2	2021-10-08	25000	20
5	Emp5	Analyst	Emp3	2021-07-11	20000	10
6	Emp6	Analyst	Emp3	2021-10-08	18000	10
7	Emp7	Clerk	Emp3	2021-07-11	15000	10
8	Emp8	Salesman	Emp4	2021-09-09	14000	20
9	Emp9	Salesman	Emp4	2021-10-08	13000	20

Query:

Output:

Emp_ID	Emp_Name	Designation	Manager_ID	DOJ	Salary	Dept_ID
1	Emp1	Director		2021-07-05	45000	10
3	Emp3	Manager	Emp1	2021-07-05	27000	10
5	Emp5	Analyst	Emp3	2021-07-05	20000	10
6	Emp6	Analyst	Emp3	2021-07-05	18000	10
7	Emp7	Clerk	Emp3	2021-07-05	15000	10

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;