UPDATE statement is one of common DML (data manipulation language) statements. Other DML statements include:

  1. SELECT
  2. INSERT
  3. DELETE

UPDATE query is used to update or modify data in a Table or a View in a database. Using this statement, you can update a single field, multiple fields, single record or multiple records. Need to use WHERE clause to limit affected rows. Without the WHERE clause, you will update all the rows in a table! Ooops! UPDATE query can also use a sub-select aka sub query.

Syntax for UPDATE query

Basic syntax for UPDATE query is:

UPDATE Table or View
SET Column1=value, Column2=value
WHERE Condition(s)

Common wisdom to use Upper Caps for SQL Keywords
More info here:
http://msdn.microsoft.com/en-us/library/ms177523.aspx

For our UPDATE queries today, we will use Northwind (link) database in SQL Server 2012. We will do the following:

  1. Update Customers table, single and multiple fields
  2. Update Customers table, multiple rows
  3. Update Orders table, multiple rows
  4. Update one table with information from another table. Orders table with info from Customers table

Update SQL script is as follows.

You can also download the complete SQL script here:

http://sqlserver2012tutorial.com/free-videos/SQL_Tutorial_SQL_UPDATE_Statement_Final.sql
/*------------------------------------------------------------------------------------------------------------

SQL Tutorial I – SQL UPDATE Statement

These example utilizie SQL Server sample database NORTHWIND 
Also we are using SQL Server 2012

Just fyi:

Lines in green starting with -- are comments
Words in blue and purple (I guess are SQL Keywords)

------------------------------------------------------------------------------------------------------------*/

--Get the data

SELECT *
FROM CUSTOMERS

SELECT COMPANYNAME, CITY, COUNTRY, REGION
FROM CUSTOMERS
WHERE COUNTRY='USA'

--Update single column, set country to America instead of USA

UPDATE Customers
SET COUNTRY='AMERICA'
WHERE Country='USA'

--(13 row(s) affected)

SELECT COMPANYNAME, CITY, COUNTRY, REGION
FROM CUSTOMERS
WHERE COUNTRY='AMERICA'

-- 13 ROWS

--Multiple columns, country and regions fields

--UPDATE CUSTOMERS

UPDATE Customers
SET COUNTRY='AMERICA', REGION='US'
WHERE Country='AMERICA'

-- (13 row(s) affected)

 

Download SQL Video Tutorial here (Right Click – Save As):

http://sqlserver2012tutorial.com/free-videos/Youtube-sql-tutorials-by-kash-1-sql-update.wmv

or from our YouTube channel:

https://www.youtube.com/watch?v=mmMzWYzG8kI