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:

 

 

Visual Studio 2008 – An Overview

Visual Studio 2008 is a development system by Microsoft. It is an integrated development environment or IDE. An IDE usually has features that help write code, manage the source code, create the object code, run it and help debug it. Visual Studio 2008 is an IDE that supports several software tools that help is each of these departments. While Visual Studio has many editions, it is from Visual Studio 2008 version that has integration with SQL Server editions, starting with the SQL 2005. There have been several versions after Visual Studio 2008 too. Because of this, connection mentioned, it made sense to study Microsoft Visual Studio 2008. Visual Studio makes available multiple tools for each of the developments, testing, deployment, integration and management of a software system for and given application. Not only, there is a comprehensive set of tool but development across devise type is also supported. Your target device could be the web, a PC, a server or some mobile device.

Visual Studio 2008 integrates tools, servers as well as services. It offers these features to make development and solving related problems relatively easy. So much so that not only experienced developers benefit from it but even beginners are able to get used to the tools and services quite easily. Productivity in development is helped by code editors that are quite efficient. Intellisense feature and wizards help developers manage their development functions. Intellisense is like auto-completion so that code statement’s completion is helped by the system automatically prompting the required parameters. That would avoid mistakes and make code development faster in Microsoft Visual Studio 2008 environment.

Life cycle management support through tools like Visual Studio Team System is available. MS Visual Studio Team system is a team collaboration product. This tool can help developers with work-item tracking and provide a team portal. By tracking the work items, it can manage version control too. Build management, business intelligence and process guidance are other features available through this component of Visual Studio 2008.

Visual Studio 2008 offers the team system that is a combination of products. These are:

  • The architecture edition is helpful in designing a distributed system that is well-designed and can be validated
  • The database edition provides a tool for managing the database and changes made to it
  • Insecure, inefficient or poor-quality code development ca be prevented through the development edition. It provides the means of unit testing
  • Test edition and test load agent modules helps with managing all the testing you need to do.

Visual Studio 2008 is available in two major editions; the standard edition and the professional edition. Microsoft Visual Studio 2008 standard edition provides an integrated environment as described. It is a complete development environment for applications for the web or the Windows.

Visual Studio 2008 Professional edition provides additional features beyond the standard edition. Developing ASP.NET or .NET AJAX based supplications for the web or for the different Windows versions, this tool will let you work with SQL Server databases as also mobile devices. This Visual Studio 2008 Pro version also has unit testing built in to get software modules tested early on. This tool helps integrate Microsoft’s productivity suite easily too.

Microsoft Visual Studio 2008 – Additional Reading

 

 

 

SQL Video Tutorial: How to use UPDATE Sql statement

 

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

 

 

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.

 

 

 

Overview of SQL Server Management Studio

SQL Server Management Studio aka SSMS can be defined as an environment that is integrated for managing the SQL Server Infrastructure. It is a combination of tools that can be used for configuring, monitoring and administering the SQL Server.  SQL Server Management Studio also makes available tools for deployment, monitoring and up-gradation of the data tier components like data warehouses and databases that are brought into use by applications and construct queries and corresponding scripts.

Benefits of SQL Server Management

There are a number of benefits of SQL Server Management Studio, the most important being that it is a comprehensive utility that manages SQL Server objects. It combines a graphical interface and capabilities related to scripting. It can also be used for managing Database Engine, Analysis Services, Integration Services, and Reporting Services

Features of SQL Server Management Studio

SQL Server Management Studio supports a number of highly developed features. The general features of the SQL Server are that it is able to support most of the administrative functions and an environment that is integrated and single allows effective authoring and management. There are dialogs that for managing various aspects like objects, Analysis and Reporting Services. Here is a screen capture of what SSMS looks like for SQL Server 2012.

 

It also supports new Scripting Capabilities. This is supported by the integrated script editors that are contained in the SQL Server Management Studio. The script editors allow authoring Transact-SQL, MDX, DMX, and XML/A. The main features that SQL Server Management Studio supports under this head are a dynamic environment allowing access to information without any loss of time, various ready templates and the ability to customize them, renders support for functions like editing and writing queries and scripts while providing a new interface that allows XML results to be viewed. The Object Explorer part of the SQL Server Management Studio filtration using the entire name, date or schema or a part of it and the asynchronous members are filtered using the metadata. SQL Server Management Studio supports plug-ins and add-ins (extensibility) for it s built upon the Visual Studio Isolated Shell. This allows usage of extensions that have been developed by third parties.


Tool Windows in SQL Server Management Studio

For all stages of development and administration, the SQL Server Management Studio provides a number of powerful tool windows. Some of these tools are capable of being used on any SQL Server part while others are limited to only specific components. The tools that can be used for all the components of SQL server and their purpose are enumerated in the table below:

 

Tool

Purpose

Can be Viewed

Object Explorer This is used for browsing servers, creating and locating objects. It is also used for managing data sources and viewing logs. This tool can be accessed from the View menu.
Solution Explorer This tool is used for storing and organizing scripts and connection information. SQL Server Scripts can be stored as Solutions and use the source control for managing scripts as their evolvement takes place. This tool can be accessed from the View menu.
Template Explorer This is used for creating queries that are based on the templates that exist. Custom queries can also be created and alter the templates that are existing. This tool can be accessed from the View menu.
Dynamic Help This is used for displaying a list of Help topics that are related. For viewing a particular topic, one needs to click on the name of the specific component.

The tools in the SQL Server Management Studio work with each other. To explain it with an example, a server can be registered with the Object Explorer and for opening a SQL Editor window that is connected to a specific database.

We provide SQL Server 2012 Tutorial videos including topics on Administration, Development, Programming and Business Intelligence areas. Feel free to check sample training at this page: http://sqlserver2012tutorial.com/free-video-training/

 

 

 

 

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: