Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: UPDATE

SQL Server offers an OUTPUT command to capture data that is changed, either through updating, deletion or insertion. The ‘before’ and ‘after’ images of the data are stored in system tables named DELETED and INSERTED. It is possible to query the applicable table to retrieve data that has been deleted or inserted by your sql statement. In the case of updates, the old data can be found in DELETED and the newly written data in INSERTED.

The following example demonstrates the process. An Audit table is created to store the results. An UPDATE is then issued against a number of columns. OUTPUT writes the old and new versions of the data (along with the id of the row changed and the date and time) to the audit table. Adding the output clause to stored procedures will allow you to audit all data changes in your database, although there will obviously be some overhead due to additional writes.

CREATE TABLE Audit_Table (CustomerID varchar(12),
                         OldAddress1 varchar(50),
                         OldAddress2 varchar(50),
                         OldAddress3 varchar(50),
                         OldAddress4 varchar(50),
                         OldAddress5 varchar(50),
                         OldPostCode varchar(12),
                         NewAddress1 varchar(50),
                         NewAddress2 varchar(50),
                         NewAddress3 varchar(50),
                         NewAddress4 varchar(50),
                         NewAddress5 varchar(50),
                         NewPostCode varchar(12),
                         DateChanged datetime);

UPDATE CustomerTable
SET Address1 = '99 My Road',
      Address2 = 'My Area',
      Address3 = 'My City',
      Address4 = 'My County',
      Address5 = 'My Country',
      PostCode = 'AA1 1AA'
OUTPUT INSERTED.CustomerID,
	    DELETED.Address1 AS 'OldAddress1',
	    INSERTED.Address1 AS 'NewAddress1',
	    DELETED.Address2 AS 'OldAddress2',
	    INSERTED.Address2 AS 'NewAddress2',
	    DELETED.Address3 AS 'OldAddress3',
	    INSERTED.Address3 AS 'NewAddress3',
	    DELETED.Address4 AS 'OldAddress4',
	    INSERTED.Address4 AS 'NewAddress4',
	    DELETED.Address5 AS 'OldAddress5',
	    INSERTED.Address5 AS 'NewAddress5',
	    DELETED.stu_capc AS 'OldPostCode',
	    INSERTED.stu_capc AS 'NewPostCode',
	    GETDATE() AS 'DateChanged'
INTO AuditTable
WHERE CustomerTable.CustomerID = 'AB123456';
Advertisements

Tags: , , , , ,

For this demo, you will require SQL Server with the AdventureWorks database and a copy of Windows Powershell. We intend to build up a number of UPDATE statements based on data within AdventureWorks.HumanResources.Employee. We want to give all 157 production technicians an extra ten vacation hours, but need an easy way of generating the many lines of SQL we require. First of all, we need to run the following query to return the employeeID of all employees who meet this criteria:-

USE AdventureWorks
SELECT EmployeeID
FROM HumanResources.Employee
WHERE Title LIKE 'Production Technician%'

With a fresh installation of the AdventureWorks database, this should return 157 results. Create a file on the root of C: called myfile.txt and copy and paste in the results using notepad or other text editor. You should have one EmployeeID per line.

Now we can build up our UPDATE queries by using Windows Powershell. Open Powershell and enter the following:-

get-content c:\myfile.txt | Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"} | out-file c:\output.txt

If you now check the newly created file (ouput.txt) on C: drive, you should have 157 UPDATE statements which you can copy and paste into SQL Server. Executing will give the production assistants their extra hours.

output.txt

Now to explain the powershell code…..

get-content c:\myfile.txt

simply reads the contents of our file. If you solely run this part of the command in Powershell, the contents of the file will be displayed on screen.

The main part of our command is the foreach loop which builds our SQL statements by adding our text before and after the contents of each row. The contents of each row is held in “$_

Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"}

The final part of our code simply writes the changes out to our output.txt file

out-file c:\output.txt

There are much more elegant ways of writing our SQL statement. We could of course, have written a single UPDATE statement and included,

WHERE Title LIKE 'Production Technician%'

…. but that would not have been as much fun and you would not have just learnt this handy Powershell tip!!

Tags: , , , , , , ,

As you are probably aware, updating data in a table can be achieved with a relatively simple SQL statement. In the following example, the data we want to change is included in the statement itself:-

UPDATE employees
SET last_name = 'Smith'
WHERE employee_id = 6392

We feed in ‘Smith’ as the new data to be entered in the last_name column, and we specify ‘6329’ as the record to be updated. This works well, but is usually only useful for tasks where only a few records need to be updated, or an entire column needs to be updated with the same data (achieved by excluding the WHERE clause).

Much more useful is the ability to UPDATE data based on the contents of another table or data source. This allows us to update large amounts of data and ensures its integrity. There are two approaches for performing updates based on data from another table.

METHOD 1

The example below updates the primary_address in the employees table to that of the home_address in the addresses table. The WHERE clause ensures that the rows in the employees table are updated with the correct data from the addresses table by joining on the employee_id column, which is present in both tables.

UPDATE employees
SET e.primary_address = a.home_address
FROM employees e, addresses a
WHERE e.employee_id = a.employee_id

With this method, it is possible to update additional columns and to expand the WHERE clause to limit the records which are altered. In the following, we add an additional two columns to be updated and a sub-query to limit the update to those staff who have commenced employment within the last year:-

UPDATE employees
SET e.primary_address = a.home_address,
e.secondary_address = a.next_of_kin_address,
e.primary_telephone = a.home_telephone
FROM employees e, addresses a
WHERE e.employee_id = a.employee_id
AND e.employee_id IN
(SELECT employee_id
FROM employment_dates
WHERE [start_date] > DATEADD(YEAR, -1, GETDATE()))


METHOD 2

This approach uses a sub-query to select the home address from the addresses table. Don’t forget to include the WHERE EXISTS clause, followed by the repetition of the sub-query, otherwise you will receive errors – this is a common mistake!

UPDATE employees
SET e.primary_address =
(SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)

As you can see from this statement above, the second method requires more code and in my opinion is much more difficult to understand. Therefore, I tend to use the first method in most circumstances. An additional limitation is that it is not easy to update multiple columns. Oracle (and a number of other database management systems) allows multiple columns to be updated by specifying the columns within brackets:-

UPDATE employees
SET (e.primary_address, e.secondary_address, e.primary_telephone) =
(SELECT a.home_address, a.next_of_kin_address, a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address, a.next_of_kin_address, a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)

However, the above does not work with SQL Server (at least not in 2005)!

To achieve this with SQL Server, you would need to write the query for each column you want to update.

UPDATE employees
SET e.primary_address =
(SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id),
e.secondary_address =
(SELECT a.next_of_kin_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.next_of_kin_address
FROM addresses a
WHERE e.employee_id = a.employee_id),
e.primary_telephone =
(SELECT a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)

NOT NICE! And this does not even include the clause to limit the updates to recently employed staff.

Tags: , , , ,

Being able to replace characters in a string of text can be useful in solving a variety of problems. Thankfully SQL Server provides a REPLACE command which allows for sections of a string to be replaced with another set of characters.

Here is a simple example to demonstrate how REPLACE works:-

 SELECT REPLACE('My name is Fred', 'Fred', 'Frank')

Replace looks at the text string and replaces any instances of ‘Fred’ with ‘Frank’. Therefore, the output of the query will read:-

My name is Frank

When used in conjunction with an UPDATE command, we can use REPLACE to alter characters within a column. In this example we are changing the monthly salary of all staff from British pounds to US dollars. We are not converting the salaries, but simply changing the ‘£’ to a ‘$’, so in fact everyone is receiving a pay cut!

UPDATE salary_table
SET monthly_salary = REPLACE(monthly_salary, '£', '$')

Instead of applying the change to all members of staff, what we should do is use a WHERE clause to ensure the REPLACE only affects those staff who should be getting paid in dollars:-

UPDATE salary_table
SET monthly_salary = REPLACE(monthly_Salary, '£', '$')
WHERE nationality = 'American'

If we check the salary_table now, then we will find that records such as,

Bob Jones            American             £2,500

will now read as follows,

Bob Jones            American             $2,500

Tags: , , , , ,

%d bloggers like this: