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 tobackup 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.

 

 


 See also