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