The UPDATE statement in SQL

A short video covering the UPDATE statement in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug                                         -- get code card up here
-- Douglas Kline
-- 3/21/2016
-- UPDATE - how to change data

USE Northwind

-- might want to review the SELECT statement
-- before using UPDATE
-- in particular, review the WHERE clause,
-- because UPDATE uses the WHERE clause

-- public service announcement
/* ***WARNING!*** *****************

*INSERT, UPDATE and DELETE are *data modifying* commands

*INSERT, UPDATE and DELETE can do set-at-a-time operations

**this means that a seemingly simple statement
** can change millions of records

* the database engine will not ask "Are you sure?"
* there is no simple "Undo"

* professionals normally don't, at the command line,
* execute INSERT, UPDATE, and DELETE commands
* on a production database

* treat these statements with respect!

*************************************/

-- the UPDATE statement changes existing rows
-- UPDATE changes records, not tables
-- again, the row must already exist

-- let's look at the Orders table

SELECT *
FROM   Orders
ORDER BY orderID desc

-- now insert a bogus row to play with:
INSERT INTO Orders   (ShipCity, CustomerID)
VALUES               ('Akron', 'VINET')

-- see what happened, and note the OrderID
SELECT *
FROM   Orders
ORDER BY orderID desc

-- OrderID=11099 for the new record

-- now that we have a row to modify, here's a simple update
-- change the orderdate to right now

UPDATE Orders
SET    OrderDate = GETDATE()
WHERE  orderID = 11099

-- and check the record
SELECT *
FROM   Orders
ORDER BY orderID desc

-- note that one record was affected
-- because the primary key was set to a single number
-- most possible damage is a single record

-- let's change two fields
UPDATE Orders
SET    RequiredDate = '20160401 17:00:00.00',
       ShipName     = 'Douglas Kline'
WHERE  orderID = 11099

-- and check the record
SELECT *
FROM   Orders
ORDER BY orderID desc

-- so the general form of the UPDATE statement is
/*
UPDATE   <table name>
SET      <assignment statement list>
WHERE    <logical expression>

or

UPDATE   <table name>
SET      <field name 1> = <expression for new value>,
         <field name 2> = <expression for new value>,
         <field name 3> = <expression for new value>,
         ...
WHERE    <logical expression>
*/

-- another example
UPDATE   Orders
SET      Freight        = Freight + 25.0,
         RequiredDate   = DateAdd(week, 1, RequiredDate),
         ShipCountry    = 'U' + 'S' + 'A'
WHERE    CustomerID     = 'VINET'
  AND    EmployeeID     IS NULL

-- and check the results
SELECT *
FROM   Orders
ORDER BY orderID desc

-- notice a few things
-- several fields are set in the same statement
-- the new values are expressions, rather than literala values
-- the new value for the requiredDate field is a function of the existing value
-- the WHERE clause is logical expression
-- potentially, many records could be updated with this single statement


-- what happens if the UPDATE would violate the table constraints
-- here's the <simplified> definition fo the Order table

/*
CREATE TABLE Orders
 OrderID        int IDENTITY(1,1) NOT NULL,               <-----******
 CustomerID     nchar(5)          NULL,
 EmployeeID     int               NULL,
 OrderDate      datetime          NULL,
 RequiredDate   datetime          NULL,
 ShippedDate    datetime          NULL,
 ShipVia        int               NULL,
 Freight        money             NULL CONSTRAINT [DF_Orders_Freight]  DEFAULT ((0)),      <-----*****
 ShipName       nvarchar(40)      NULL,
 ShipAddress    nvarchar(60)      NULL,
 ShipCity       nvarchar(15)      NULL,
 ShipRegion     nvarchar(15)      NULL,
 ShipPostalCode nvarchar(10)      NULL,
 ShipCountry    nvarchar(15)      NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
 OrderID ASC
)
*/

UPDATE   Orders
SET      OrderID = 500000
WHERE    OrderID = 11099

-- Cannot update identity column 'OrderID'.
-- IDENTITY columns are not update-able

UPDATE   Orders
SET      CustomerID = 'Barney'
WHERE    OrderID = 11099

-- String or binary data would be truncated.
-- CustomerID is nchar(5), and 'Barney' is 6 characters

UPDATE   Orders
SET      CustomerID = 'Barne'
WHERE    OrderID = 11099

-- The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Orders_Customers". 
-- The conflict occurred in database "Northwind", table "dbo.Customers", column 'CustomerID'.
-- that would violate referential constraint
-- 'Barne' doesn't exist in the Customers table

UPDATE   Orders
SET      CustomerID = 'VINET'
WHERE    OrderID =  50000

-- no such record, 0 rows affected
-- no error - you need to check rows affected if it's important

-- in summary,
-- UPDATE modifies fields in existing rows
-- specify values according to their data type - you have to know the data types
-- various things could "block" an UPDATE
-- these "blocks" are your friends! they keep your data clean!
-- sometimes zero rows are updated - that's okay
-- sometimes millions of rows are updated!
-- treat INSERT, UPDATE, and DELETE with respect!


-- Database by Doug
-- Douglas Kline
-- 3/21/2016
-- UPDATE - how to change data

/*
DELETE FROM  Orders
WHERE  OrderID > 11077


*/