SQL 2012 Update Statement – How to modify data in SQL?

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.

 

SQL-Server-2012-Update-TSQL

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.

UPDATE PhoneBook
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 – Structured Query Language

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:

  • Reading record(s) or parts of them
  • Modifying record and store back
  • Deleting record(s)
  • Others



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.

Further Reading on SQL:

 

 

Transact SQL scripts – SQL 2012 Video Tutorials

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!)

Transact SQL scripts:


 

More information on this can be found on this page SQL 2012 Video Tutorials – Unlimited Package.

 

 

 

Top 5 Transact SQL Commands in SQL Server 2012

SQL Server 2012 has quite a few new Transact SQL Commands. We are going to discuss Top 5 of these SQL statements, here are they:

  1. SEQUENCE
  2. NEW Date functions
  3. New Logical functions
  4. CONCAT TSQL
  5. Format command in SQL 2012

 

SEQUENCE in MS SQL 2012

Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012! A Sequence object is an object that provides functionality similar to Identity (Autonumber) column. The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value of the ID column before you insert records. We are including some examples here that you can use with SQL Server 2012.

–By Itself

create sequence dbsequence
start with 1 increment by 5

select next value for dbsequence

Returns 1

select next value for dbsequence

Returns 6

drop sequence dbsequence

–With a table

create sequence idsequence
start with 1 increment by 3

create table Products_ext
(
id int,
Name varchar(50)
)

INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, ‘ProductItem’)

select * from Products_ext

/* If you run the above statement two types, you will get the following:-

1    ProductItem
4    ProductItem

*/

drop table Products_ext
drop sequence idsequence

Date Functions in MS SQL 2012

There are a number of new date functions in Microsoft SQL Server 2012. We are going to discuss two of the following functions:

  1. DATEFROMPARTS Function
  2. EOMONTH Function

DATEFROMPARTS function takes three integer parameters and builds a date data type out of them. Here is some SQL 2012 Transact SQL that shows you the DATEFROMPARTS date function in action:

declare
@year int,
@month int,
@day int

set @year=2012
set @month=9
set @day=28

select datefromparts(@year, @month, @day) as mydate

/*
mydate
———-
2012-09-28
*/

EOMONTH Function: Many applications need the last day of the month for a specific month. EOMONTH Function lets you do just that. Lets check out the following Sql statements:

declare @mydate datetime
set @mydate=’07/23/12′

select eomonth(@mydate)
2012-07-31

Other useful datetime functions in SQL Server 2012 are:

-TIMEFROMPARTS
-DATETIMEFROMPARTS
-SMALLDATETIMEFROMPARTS
-DATETIMEOFFSETFROMPARTS

 

New Logical functions in MS SQL 2012

Just like the Data functions, SQL Server 2012 has new features in regards to Logical functions as well. We will take a look at two of them: CHOOSE and IIF SQL functions.

CHOOSE is a simple function and returns the specified index from a list of values. Here are two examples:

Select CHOOSE(2, ‘Kash’, ‘Money’) –Money is second value
Select CHOOSE(1, ‘Kash’, ‘Money’) –Kash is first value

IIF function which is quite popular in MS Office applications is not available in SQL Server 2012. This function takes three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result. In other words, it kinda works like a case statement. Here is the TSQL syntax with examples:

–select iif(first argument, second argument, third argument)

select IIF(100>50, ‘true’, ‘false’)

Returns true

select IIF(100<50, ‘true’, ‘false’)

Returns false

CONCAT Transact SQL

When you need to combine different text values in SQL Server 2012, you can use the new feature CONCAT. This function helps you concatenate two or strings together. Here is my name with CONCAT function!!

SELECT CONCAT (‘Kash’, ‘Money’, ‘Millionaire’)

Returns KashMoneyMillionaire

–Also can be used in a table, using employees from Northwind database

select * from employees

select concat(firstname, ‘ ‘, lastname) as FullName
from employees

FORMAT command in MS SQL 2012

Using FORMAT command is especially helpful when you are working with dates in SQL Server 2012. Syntax is as follows:

FORMAT (value, format [,culture])

Lets go ahead and see the Format command in action.

SELECT
GETDATE(), –native date
FORMAT (GETDATE(), ‘d’), –without leading zero
FORMAT (GETDATE(), ‘dd/MM/yyyy’) –custom format

/*

Output for this is:

2012-08-20 10:36:59.247    8/20/2012    20/08/2012

*/

If you would like to learn more, check out our page on SQL Server 2012 Tutorial.

 

 

 

Follow us on: