Here's a query sample that combines a few different things. It's for MS SQL Server, YMMV.
Touch the LastChanged field (a datetime) of the oldest 0.5% of all member records that haven't made a purchase in 365 days. Note the two field join on MemberID and SiteID to the subselect.
UPDATE Members
SET LastChanged = GETDATE()
FROM Members M INNER JOIN (
SELECT TOP 0.5 PERCENT MemberID, SiteID FROM Members
WHERE Lastsale < GetDate() - 365
ORDER BY LastChanged
) M1 ON M.MemberID = M1.MemberID AND M.SiteID = M1.SiteID
And this sample with the update based on a second table:
UPDATE t1
SET field1 = t2.someotherfield
FROM table1 t1
JOIN table2 t2 ON t1.someid = t2.someid
WHERE t2.myfield = 'test'
-- For multiple columns just AND them: JOIN table2 t2 ON t1.someid = t2.someid AND t1.otherid = t2.otherid
Based on http://www.tek-tips.com/viewthread.cfm?qid=1509886
And a delete:
DELETE #tmpPhantom
FROM #tmpPhantom A INNER JOIN (SELECT SKU, SiteID FROM InventoryLocation WHERE OnHand = 0) B ON A.SKU = B.SKU AND A.SiteID = B.SiteID
tags: subquery, subselect, sqlserver, syntax, update, query,