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 

Output:

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

SELECT *
 FROM TAE2

Output:

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:

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

However, the contents of TAE2 remains unchanged.

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.

Query:

Output:

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;