SQL Update

My Learning: Log in Learning Report [Log in]


Remove Ads or Log in
Remove Ads or Log in
+2  |  -0
Hi

In this tutorial I will give you example of SQL Update queries which will include SQL update join examples. I will use SQL Server 2008 R2 for my samples which should work with previous version of SQL Server without any problems.

Contents

SQL Update Overview


SQL Update is used to update one table columns with specified values. You can update one or multiple columns at the same time. For instance you might want to update customer address and this case you would update several columns like AddressLine1, AddressLine2, City, Post code with usually either hard coded or provided values from end user application. You could also update values using calculated fields for instance you might want to update Employee holiday entitlement (once a year) and you could use Employee start date to calculate number of years that employee has worked for the company and use some "IF" (case in SQL) logic to specify the correct holiday entitlement. Very often you might want to perform updates using another related table and for instance in our Holiday entitlement case we could use a HolidayEntitlement table and match it against our YearsWorked that could be calculated using EmployeeStartDate and provide matching row (HolidayEntitlement) from related HolidayEntitlement table. This SQL Update Join that I will give you examples of but be careful with joins this method can be tricky and I will give examples when it can go wrong. Apart from specifing or matching rows in SQL Update the most common task is to specify correct filter (or join) so we update only the rows with values we want to affect. Below I will provide you with simple syntax and later on provide you examples that will cover types of updates I mentioned.

SQL Update Syntax

Below I will give you basic and most popular example of SQL Update syntax which should work in most cases but there are more complicated syntaxes and I will show them to you with examples and screenshots later in this section.

Remember you can only update one table at a time but you can update multiple fields at the same time by separating them using commas.

Syntax
UPDATE tableName
SET ColumnName = NewValue
,ColumnName2 = NewValue2
WHERE ColumnName = FilterValue

SQL Update Simple Example

In the example below we change Katie's surname to Glownia based on her CustomerID which is 2
SQL Update Statement

The above example is the most popular SQL update and in our sample we used simple condition.

TIP: Very often you will want to perform updates on single or multiple rows. The best approach to ensure you update correct rows is to perform SELECT with the same conditions before you perform the actual update as it is not easy to "undo" your updates.

Warning: Make sure you use WHERE clause otherwise you might end up accidentely updating all values like in the example below (which occasionally is intentional)
Warning: See below nonsense example.

SQL Update Without Filter

SQL Update Join


It is also fairly common to perform SQL Update Join which means that we will update one table using another table and join condition. Below is an example where I have a customer table and I have UpdatedCustomer table that contains 'latest' customer details let say from another source system. In my case I want to update my Customer table using the latest data. In order to do that I perform join between my udpate target table and my source table using join on customerID. Notice the FROM I use the same table as in the update line; this is how it know how to 'map' rows for the update.

SQL Update Join Example

Warning: Updates using join can be sometimes tricky. If you have join that produces more than one match for the target table then you may get unexpected results without warning! See below example

SQL Update Join Example

Warning: Wrong join and you end up with nonsense results. See below

sql-nonsense-join

External Links
SQL Update (Microsoft)
SQL Update (wikipedia)


Take care
Emil

Remove Ads or Log in

We need you!

By becoming a proud member we can:
  • Switch off annoying ads
  • Create more articles and videos
  • Develop more useful features supporting your learning
  • Provide FREE webinars (for members)
  • Feel our hard work since 2011 is appreciated
Become a proud member and get exclusive features.

If 2% of our 64,943 visitors (Last 30 days) become a proud member than we can quit our jobs and work full time on this website. We started membership in May 2014 and so far 0.07% of our visitors are members and we would like to say THANK YOU to those who become a proud member and support our efforts.

Thanks to members:
  • Education is for FREE and we create more FREE articles and videos
  • We provide new features (members) that can speed up learning.
  • 53% of our page views (82,000 each month) do not have page ads.
  • We switched video ads from 14% of our videos (50 out of 345).
We have invested 4 years of our time in this website and we estimate it will take about 5 years before we can quit our jobs and dedicate all our time to develop considerably our website which should help to truly achieve our vision of free education for everyone; all thanks to support of our members only. Become a proud member

Visitor Voting

Do you enjoy your job?
Visitor Voting: Yes +15  |  No -5
Add comment (no email required).



Add Comment
Name: Ethi
Date Posted: 5/27/2014 2:26:14 PM
Comment: I feel your Article very useful and new programmers also understand your article thank you so much..............




Are you a member?

If yes than please log in

Learning Map is for members with Bronze, Silver and Gold Membership.
For more information visit membership options.


Close this window , log in or learn more about membership benefits
  Become a Proud Member