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;