SQL Server: UPDATE Statement

This SQL Server tutorial explains how to use the UPDATE statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) UPDATE statement is used to update existing records in a table in a SQL Server database. There are 3 syntaxes for the UPDATE statement depending on whether you are performing a traditional update or updating one table with data from another table.

Syntax

The syntax for the UPDATE statement when updating one table in SQL Server (Transact-SQL) is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
[WHERE conditions];

The syntax for the UPDATE statement when updating one table with data from another table in SQL Server (Transact-SQL) is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
[WHERE conditions];

The syntax for the SQL Server UPDATE statement when updating one table with data from another table is:

UPDATE table1
SET table1.column = table2.expression1
FROM table1
INNER JOIN table2
ON (table1.column1 = table2.column1)
[WHERE conditions];

Parameters or Arguments

column1, column2: The columns that you wish to update.
expression1, expression2: The new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
WHERE conditions: Optional. The conditions that must be met for the update to execute.

Example - Update single column

Let's look at a very simple SQL Server UPDATE query example.

For example:

Seguir leyendo »

SQL Jobs on a calendar

SQL Server

https://thelonedba.wordpress.com/2016/04/30/sql-jobs-on-a-calendar

by thomasrushton

SQL Server backup jobs are IO-intensive, he says, stating the obvious.

When you have a lot of servers which have been set up over the years, and you’re new to the environment, and want to see when storage is being hammered , you might end up thinking that a calendar view might be of help – I know I did.

So all I had to do was figure out a query to look at SQL Server scheduled task history complete with start & end times (which I’d already got), and then get those into a series of appointments in Outlook so I could see when the busy times were.

Step 0: Registered Servers Groups

Step 0? Well, this isn’t directly related to what I’m doing, but helps a lot.

If you don’t know about Registered Servers, then go and find out about them now. I’ll wait. They’re a great way of logically grouping your server estate and allow you can run queries against multiple servers at the same time pulling results back into a single result set.

I’m using them from a Central Management Server; this provides a single shared location that gives the whole team a view of the SQL Server Estate, while removing the overhead of everyone keeping their own lists.

Start working with Registered Servers groups by launching SQL Server Management Studio, and hitting CTRL+ALT+G or selecting “Registered Servers” on the “View” menu.

(I’ve blocked out the actual server name).

As you can see, we have six groups of servers defined. What you can’t see is that there are folders within those folders that contain multiple servers, so you can keep more organised lists. More of how this stuff works another time. Maybe.

I’m interested in the top tier “gold standard” servers, so I’ll select “1 Gold”, and click to create a new query. This will take a while – a single query window will open and attempt to connect to all the SQL Servers registered in that group.

Step 1: Query for SQL Job Runtime Information

Of course, this assumes that we’re holding on to SQL Server Agent Job History for a little while – the default job history retention settings might be too aggressive to allow you to get history going back any reasonable length of time.