Transact SQL 101 - Lesson 06_02_SQL.txt

USE adventureworkslt
GO

SELECT * FROM saleslt.customer


--Column, Column
--UPDATE (Example 1)
SELECT * FROM saleslt.address WHERE stateprovince = 'Texas'

UPDATE saleslt.address
SET stateprovince = 'TX'  
WHERE city = 'Dallas'

UPDATE saleslt.address
SET PostalCode = '75204', CountryRegion = 'USA'
WHERE city = 'Dallas'
WHERE AddressID IN (25,572,574,577,581,588)

UPDATE saleslt.address
SET stateprovince = 'Texas', PostalCode = '75201', CountryRegion = 'United States'  
WHERE city = 'Dallas'

--Column from same table
--UPDATE (Example 2)
UPDATE saleslt.address
SET addressline2 = addressline1
WHERE addressid = 11380

SELECT * FROM saleslt.address
WHERE addressid = 11380

--UPDATE (Example 2)
UPDATE saleslt.address
SET addressline2 = DEFAULT
WHERE addressid = 11380


--Keyword/Expressions/Functions
--UPDATE (Example 3)
UPDATE saleslt.address
SET modifieddate = GETDATE()
WHERE addressid = 11380

SELECT * FROM saleslt.address
WHERE addressid = 11380

UPDATE saleslt.address
SET modifieddate = '2002-07-01'
WHERE addressid = 11380

--Subquery that returns a single value
--UPDATE (Example 4)
UPDATE saleslt.product 
SET productnumber = 
  (SELECT [name] FROM saleslt.productmodel WHERE productmodelid = 6)
WHERE productid = 680

SELECT * FROM saleslt.product
WHERE productid = 680

UPDATE saleslt.product
SET ProductNumber = 'FR-R92B-58'
WHERE productid = 680


--UPDATE (Example 5)
--FROM Clause (Updating Multiple Rows at a time)
UPDATE customer
SET customername = AddressLine1
FROM customer INNER JOIN address ON customer.addressid = address.addressid
--OR I can write it like this...
FROM address INNER JOIN customer ON customer.addressid = address.addressid
--OR I can write it like this...
FROM address WHERE customer.addressid = address.addressid