SQL 2000 Transact - UPDATE Statement

SQL Transact UPDATE Statement

One you have mastered SELECT, its time to turn your attention to SQL's UPDATE statements.

Lesson 1 - UPDATE .. SET

The scenario: You prices must rise! You need to increase prices in line with inflation, you have held down the price for two years, but now is the time for a 5% rise

Preparation

Let us find carry out a preliminary investigation.

-- SQL statement to check the prices before the increase

USE Northwind
SELECT productname, unitprice
FROM products
GO

Learning points

Note 1: Check you are using the pleural productS, as the name of this table.

Note 2: This is standard practice to run a SELECT before the UPDATE.

Before and after.

Make a physical, or mental note of the Aniseed Syrup 10.00?

UPDATE.... SET statement

-- SQL statement to UPDATE all prices by .05 = 5%

USE Northwind
UPDATE products
SET unitprice = (unitprice *1.05)
GO

-- Check on Aniseed Syrup - was 10.00

USE Northwind
SELECT productname, unitprice
FROM products
WHERE productname = 'Aniseed Syrup'
GO

Learning Points

Note 1: Update needs the SET counterpart.

Note 2: Check the syntax, particularly the brackets,
unitprice = (unitprice *1.05)

Note 3: By using GO, we executed two statements in one session.

After the event check

How much was the Aniseed Syrup after the price increase?

-- SQL statement to double check the price

USE Northwind
SELECT productname, unitprice
FROM products
WHERE productname = 'Aniseed Syrup'
GO

Guy's Out Takes

You may like to backup Northwind before tackling the UPDATE statement. Certainly you should backup in the 'real world' before a batch update of rows.

SQL Transact Out Take

What is the problem with this SQL statement?

-- SQL statement to double check the price

USE Northwind
SELECT productname, unitprice
FROM products
WHERE productname = "Aniseed Syrup"
GO

Answer: Wrong type of speech marks it should only be a single quote mark

'Aniseed Syrup'; SQL transect doe not support double quotes in WHERE clauses, "Aniseed Syrup" would be incorrect.

Guy Recommends: GFi EventsManager

Let GFI EventsManager do the dirty work! Have event logs monitored automatically and get warned about critical events! Download a copy here

Please report a broken link, or an error.