Examples with sample data
These are test tables we are using:
-- Parent table CREATE TABLE #customers ( ID INT IDENTITY(1,1), CustomerCode VARCHAR(10), ItemCount INT ) -- Child table CREATE TABLE #items ( ItemId INT IDENTITY(1,1), ItemType CHAR(1), CustomerId INT, Price MONEY, PurchaseDate DATETIME ) -- Insert some sample data. INSERT INTO #customers VALUES ('Beret',0) INSERT INTO #customers VALUES ('Zuma',0) INSERT INTO #items VALUES ('Keyboard', 'A', 1, 5.99, '20130122', '') INSERT INTO #items VALUES ('Monitor', 'A', 1, 60.25, '20121008', '') INSERT INTO #items VALUES ('Mouse', 'B', 1, 12.0, '20120317', '') INSERT INTO #items VALUES ('MousePad', 'B', 1, 3.99, '20120317', '') INSERT INTO #items VALUES ('Cell', 'C', 1, 23.80, '20120206', '') INSERT INTO #items VALUES ('Lamp', 'A', 2, 20.60, '20120905', '') INSERT INTO #items VALUES ('Headphones', 'C', 2, 9.99, '20130122', '') -- An example goes here: -- ... -- Clean up DROP TABLE #customers DROP TABLE #items GO
Example #1: Mark the items that have the highest price within their type groups:
UPDATE #items SET Comment = 'Top price for ' + ItemType WHERE Price = (SELECT MAX(Price) FROM #items it WHERE it.ItemType=#items.ItemType GROUP BY it.ItemType) SELECT ID, ItemType, Price, Comment FROM #items
-- Before update ID ItemType Price Comment ----------- -------- --------------------- -------------------- 1 A 5.99 2 A 60.25 3 B 12.00 4 B 3.99 5 C 23.80 6 A 20.60 7 C 9.99 -- After update ID ItemType Price Comment ----------- -------- --------------------- -------------------- 1 A 5.99 2 A 60.25 Top price for A 3 B 12.00 Top price for B 4 B 3.99 5 C 23.80 Top price for C 6 A 20.60 7 C 9.99
Example #2: Mark the items with duplicated dates:
UPDATE #items SET Comment='Duplicated Date' FROM #items it WHERE (SELECT COUNT(*) FROM #items it2 WHERE it2.PurchaseDate=it.PurchaseDate GROUP BY it2.PurchaseDate) > 1
-- Before update ID ItemName Comment ----------- ---------- ---------- -------------------- 1 Keyboard 01/22/2013 2 Monitor 10/08/2012 3 Mouse 03/17/2012 4 MousePad 03/17/2012 5 Cell 02/06/2012 6 Lamp 09/05/2012 7 Headphones 01/22/2013 -- After update ID ItemName Comment ----------- ---------- ---------- -------------------- 1 Keyboard 01/22/2013 Duplicated Date 2 Monitor 10/08/2012 3 Mouse 03/17/2012 Duplicated Date 4 MousePad 03/17/2012 Duplicated Date 5 Cell 02/06/2012 6 Lamp 09/05/2012 7 Headphones 01/22/2013 Duplicated Date
Example #3: Count the number of items for each customer:
UPDATE #customers SET ItemCount = (SELECT COUNT(*) FROM #items it WHERE it.CustomerID=#customers.ID GROUP BY it.CustomerID) FROM #customers SELECT * FROM #customers
-- Before update ID CustomerCode ItemCount ----------- ------------ ----------- 1 Beret 0 2 Zuma 0 -- After update ID CustomerCode ItemCount ----------- ------------ ----------- 1 Beret 5 2 Zuma 2
Other examples:
-- UPDATE ... FROM ... JOIN UPDATE cust SET cust.CustomerType=5 FROM #customers cust JOIN CustomerOrder ord ON cust.ID=ord.CustomerID WHERE CustomerType=0