1.
Source: Safalta.com
Introduction to Digital Marketing2. Website Planning and Creation
The SQL UPDATE statement's syntax is seen below.
UPDATE TableName
SET ColumnName1 = Value,
ColumnName2 = Value
[FROM = expressions]
[WHERE Condition]
While executing SQL UPDATE statements, we must exercise caution at all times. Make sure to include the WHERE clause in the Change statement if you need to update a specific data field using conditional expressions; otherwise, all data fields from the supplied columns will be updated if you run the UPDATE statement without a WHERE clause. Rows that are being updated by a UPDATE statement are under an exclusive lock, which is released after the transaction is complete. Depending on the degree of isolation, lock level behaviours might alter. Executing UPDATE statements in batches and checking that any JOIN statements or condition-based filtering is supported by the appropriate indexes are the best practises for avoiding higher-level locks. If you do not properly prepare larger upgrades, it may potentially have a detrimental performance impact. To avoid the negative impact of this operation, perform it in batches during non-business hours. One thing to remember when using aliases in the UPDATE statement is that all references to table expressions must match in the UPDATE statement, so if you use an alias for a table expression, be sure to use it elsewhere in the SQL UPDATE statement.
Using SQL UPDATE syntax
Various SQL UPDATE statement use cases will be explained in this section. You will get proficient with SQL UPDATE statements.changing a single field
- to alter some fields
- To change every column's value
- Using the date-time function, change the current date-time value GETDATE
- to change a data field that was filtered by a certain condition
- to change data from a different table
If you want to change one or more data fields, use the SQL UPDATE statement.
Let me first demonstrate our source table, where I will do all of the changes.
If you don't already have a table, you may create one and add some values to practise and understand different SQL UPDATE syntax usages.
In the database TESTDB, an Employee table is created, and the data in it is displayed in the image below.
To change any column, all you need to do is use the SQL UPDATE syntax with the necessary arguments.
Consider changing the employment status of every employee to 1.
To do so, all you would need to do is run a quick UPDATE statement on this column.
If you have a requirement similar to the one below, use the T-SQL statement below.
UPDATE Employee
SET [employment nature] = 1
The result shown below demonstrates that by executing the aforementioned statement, all 17 rows have been changed.
Let's check each employee's type of employment now.
The difference is clear; in the image below, the employment nature has been modified for every employee.
Let's say you want to change only one data column for any of these people, such as their city or job title.
You may accomplish this by utilising the SQL UPDATE syntax.
If Mary has been promoted to Sr Analyst, you may change Mary's current designation by running the following query.
To change Mary's designation, I have performed the statements shown below.
UPDATE Employee
SET [designation] = ‘Sr Analyst’
WHERE ID = 3
Use distinct ids whenever possible when applying criteria to filter out the targets we want to update.
Because this query will change the designation of all workers with the name Mary if her name is used in the WHERE clause, I have used Mary's ID or employee ID to update her particular information.
In the screenshot below, we can see that the UPDATE statement has been successfully executed along with the second set of lines that are there to fetch Mary's information after changing her designation.
Her title is now shown as our desired value.
Additionally, we have the ability to change several information for any one employee at once.
Assume that Suresh has moved from Mumbai to New Delhi and been promoted from Senior Analyst to Specialist.
To perform both changes with only one SQL UPDATE command, use the following statement.
UPDATE Employee
| 1 2 3 |
SET [designation] = ‘Specialist’, [city] = ‘New Delhi’ WHERE ID = 8 |
See the output below, city and designation columns have been changed to our desired values. By naming the columns and their corresponding values in the aforementioned sentence, you may make changes to many columns at once.
Using date-time functions in a SQL UPDATE statement
Additionally, we may employ date-time functions with SQL UPDATE statements. If you wish to update a column with the current date and time, you may use the GETDATE method to retrieve the most recent system time and do so. In the example below, I've updated column Date, which had no data before to the update using the SQL UPDATE syntax. The second set of results displays the current date and time in this column Date. To return only the top 10 rows, you can also use the TOP query with UPDATE syntax like I did with the SELECT statement. Since I changed the entire database here, I utilised another table called Sales.| 1 2 |
UPDATE Sales SET Date = GETDATE() |
The output before and after changing the current date and time in the Date column is shown below. The current timestamp allows you to observe that the data has been changed. If you need to change particular data fields, you may apply filters once more using the WHERE clause.
In the SQL UPDATE statement above, in addition to the GETDATE() function, we may also utilise the CAST and CONVERT methods if we need to record the current date and time in a different format.
With subqueries and a JOIN statement, the SQL UPDATE syntax
Consider that you have introduced a POC (Point of Contact) column to the Sales table. The objective is to add a POC name for each sales company is doing for better service and customer experience. The Sales database already has the PersonID column, which maintains the employee identifiers who have sold that particular product. In this newly introduced column, we must now include the name of that person's ID as POC. Here, we must INSERT data into the Sales table and get information from the Employee table.To change data from a different table, execute the line below.
UPDATE Sales
SET POC = (SELECT name from employee WHERE employee.ID = sales.PersonID)
The output is shown below, showing both the original state and the modified state. POC names may be compared to their employee IDs as shown in the employee table.
The aforementioned query may also be written with a JOIN statement. I'll demonstrate how to utilise the SQL UPDATE syntax with JOIN statements in the example below.
We now have POCs allocated to each sold product. Imagine that it became necessary to add a city column to the sales table and to update this column for each transaction in accordance with the location of the POC or employee who sold the product. To do this, we'll utilise a JOIN statement using SQL UPDATE syntax and let you give an example.
UPDATE Sales
SET sales.City = employee.City
FROM Sales
JOIN Employee
ON employee.ID = sales.PersonID
To make my output result concise, I'm only showing the top 5 rows, although the query below changed this column for all the entries that were previously stored in it.
To verify if amended data is accurate or not, you may compare their cities from the first snapshot.
Conclusion
To change data fields in SQL Server, use the UPDATE SQL query. You may now comprehend how to use the SQL UPDATE statement to edit one or more data files in SQL Server by reviewing the fundamentals of the syntax and use cases covered in this article. Additionally, you learn how to utilise it with JOIN statements, subqueries, other SQL Server functions, and expressions.
