SQL Transact UPDATE StatementOne you have mastered SELECT, its time to turn your attention to SQL’s UPDATE statements. Lesson 1 – UPDATE .. SETThe 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
Learning pointsNote 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 — Check on Aniseed Syrup – was 10.00 USE Northwind
Learning PointsNote 1: Update needs the SET counterpart. Note 2: Check the syntax, particularly the brackets, 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
Guy’s Out TakesYou 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 TakeWhat is the problem with this SQL statement?
— SQL statement to double check the price USE Northwind
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