SQL Update is a command or updating a data record. SQL Update query is part of the SQL standard that is implemented by all product vendors. Thus, the concept applies uniformly across all RDBMS products. Typically, in an RDBMS based database, data is organized as rows and columns. Each row of data has fields in columns that are related. One row of data is called a record. SQL Update is a means of updating a field within a record in the database. The syntax of SQL Update command provides for specifying the particular piece of data into a database table.
The following is the syntax for the SQL Update command. The words in capitals are the keywords that must be used as is. The text strings within quote marks in the syntax of SQL Update query indicated need to be filled by actual parameter values for pinpointing the specific items to be updated.
UPDATE [table name]
SET [column name1]= ‘column value1′
WHERE [a particular column name]=’existing value of the column’
This above syntax of SQL Update specifies a specific table name on which data updates are to be made. The SET keyword lets the user specify the new values to be created in the given fields/columns of the table. The WHERE keyword specifies the existing value of a keyword so that the particular record can be pinpointed. In the absence of the WHERE keyword in the SQL UPDATE syntax, all the records will get updated. Let us follow this with an example so that the exact meaning is very apparent.
A phone book is something every one of us use. If a typical phonebook was implemented in an RDBMS a table “PhoneDirectory” could look something like the table indicated below.
A simple table like this could be updated manually. When the phone book is large, for example, the public phone book for the organization, updating will need database support. One of the most common changes that can happen is that the person may have switched over to another company. His or her work phone or home phone could change. SQL UPDATE statement provides the means to make a change to the database, specifically to a particular record in the database.
For the sake of illustrating the use of the SQL UPDATE, let us assume Robert Halden has been transferred to a different city where the ABC, Inc. has an office. In such a case both his work and home phones would change. SQL UPDATE command for making these desired changes would look like the following.
SET WorkPhonenumber=’397-478-2294′, HomePhoneNumber=’397-225-8325′
WHERE ContactName=’Robert Halden’
This SQL UPDATE statement chooses the right table in a database and substitutes the work and home phone numbers of a specific record where the contact name is Robert Halden. The update happens in just one record that is required to be updated. If some particular column value(s) for all records were to be updated, dropping the WHERE clause of the SQL UPDATE command would do the job. The SET clause will specify the column and the value to be set in the complete database.
What is SQL or Structured Query Language becomes easier to understand if we look at database systems first. A database is a repository of data vital to the activities that need large amounts of data to be stored. Businesses are examples of such activities. Usually such data is used again and again. That requires the data to be organized and easily accessible. Retrieval or update of such data is required to be accurate. Relational databases allow users to set up flexible search criteria to accurately locate a specific data item. Relational databases organize data as tables of rows and columns. RDBMS lets users create flexible queries and locate data accurately. Query language offers standardized syntax to specify such queries. When found, operations possible on the data using Structured Query Language are:
RDBMS or the relational database management systems use SQL programming language. SQL is a standardized form of proprietary query languages used by the product companies. Structured Query Language is a standard of both ANSI and ISO organizations. Standardization makes migrating easier. Substantial changes to the SQL statements used in an application should not be required when moved to another database.
Many of the database products use SQL. Major, well-known database products available are Oracle, IBM DB2, Microsoft SQL Server, etc. Open source MySQL is a very popular product. Structured Query Language available with each of these products offer the core functions. Unfortunately, these companies implement additional functions. The core functions offer select, insert, create, update, delete and drop, etc. These are really sufficient to implement most operations one needs. SQL of these products coupled with the extensions are known differently. T-SQL is offered by Microsoft in SQL Server product while the Oracle version is called PL/SQL.
Distributed database applications, typically, work with a central database server known as the backend. Client applications communicate with this back-end to retrieve data and manipulate them. These clients issue queries for data with Structured Query Language statements. The database server is then updated with the changed data.
SQL is able to change/modify data in the RDBMS. Besides, it is able to change the structure of the database tables. Structured Query Language statements will let a user create a table, modify it or even entirely delete it. What SQL statements can do with data is insert data into fields in the table. What SQL lets you do is to “select” a specific table for modifications. A SQL “update” command changes the value of a piece (field) of data.
If you need to work with RDBMS applications, the core functions that constitute what is SQL would be easy to learn. There are only a few commands that are used. Learning the syntax of how the command word and associated parameters are used is quite simple too. Even when your application needs to migrate to any other RDBMS, it should not be difficult to manage. Optimization can be implemented through the core SQL first, the product specific extensions can be done as a next step.
UPDATE statement is one of common DML (data manipulation language) statements. Other DML statements include:
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
Common wisdom to use Upper Caps for SQL Keywords
More info here:
For our UPDATE queries today, we will use Northwind (link) database in SQL Server 2012. We will do the following:
Update SQL script is as follows.
You can also download the complete SQL script here:
/*------------------------------------------------------------------------------------------------------------ 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):
or from our YouTube channel:
As part of the SQL 2012 Video Tutorials – Unlimited Package, you will have to access to Transact SQL (TSQL) scripts. These will help you in understanding SQL 2012 and how to work with objects in MS SQL 2012 database environment. Some of the SQL scripts are used to create objects like database, table, indexes, primary keys and foreign key constraints. Other SQL statements include the DML commands like: INSERT, SELECT, UPDATE AND DELETE. Here’s a list of SQL statements so far:
(Note: The ones with active links can be downloaded for FREE!)
More information on this can be found on this page SQL 2012 Video Tutorials – Unlimited Package.