UPDATE is used to update data within a table (as opposed to ALTER which is used to update the columns and tables themselves).
/* There are ways to change data rows within a table. UPDATE - updates data within a row or rows INSERT INTO - inserts rows into a table. MERGE INTO - a combination of UPDATE and INSERT. UPDATEs rows that already exist, and creates rows that don't already exist, based on some identifying value such as an ID number. SELECT INTO - creates a new table based on results from a query. New table will not have metadata such as keys or identities. */ -- UPDATE, changes a field value(s) using UPDATE and a WHERE clause. Be careful, as not including a WHERE clause will update all rows. UPDATE customers11 SET LastName = 'Smith' WHERE CustomerID = 3; -- UPDATE, changes an entire column to have a single value by using UPDATE *without* a WHERE clause (below, it will set the LastName to 'Smith' for all customers. UPDATE customers11 SET LastName = 'Smith'; -- UPDATE, changes rows based on a query, based on an identifying piece of information (in this case, c.CustomerID = SalesLT.CallLog.CustomerID) UPDATE SalesLT.CallLog SET SalesPerson = c.SalesPerson, PhoneNumber = c.Phone FROM SalesLT.CUstomer AS c WHERE c.CustomerID = SalesLT.CallLog.CustomerID; -- INSERT INTO, adds rows into specified columns - this example shows to to add values explicitly one row at a time. INSERT INTO Orders (CustomerID, OrderDate, Company, Product, Price, PromoPoints, RefundCode) VALUES (1, '1999-08-28', 'Monsanto', 'Toothpaste', 12.50, NULL, NULL), (5, '1999-08-07', 'Grainger', 'Steak', 0.55, 6236, 'AKDH'), (15, '2000-12-15', 'Laika Industries', 'Salt', 132.21, NULL, NULL), (22, '2000-10-15', 'ATT', 'Sunglasses', 12.50, NULL, NULL); -- INSERT INTO, adds rows based on a query INSERT INTO results (BAT_ID, H_CD) SELECT BAT_ID, Hits FROM events ORDER BY BAT_ID; /* MERGE INTO, updates rows that are already there, and adds new rows that don't exist (some people refer to this as an Upsert [UPDATE + INSERT]). Modifies data based on a condition: - when the source tables matches the target table - when the source has no match in the target - when the tartget has no match in the source */ MERGE INTO Production.Products AS P USING Production.ProductsStaging AS S ON P.ProductID = S.ProductID WHEN MATCHED THEN UPDATE SET P.UnitPrice = S.UnitPrice, P.Discontinued = S. Discontinued WHEN NOT MATCHED THEN INSERT (ProductName, CategoryID, UnitPrice, Dicsontinued) VALUES (S.ProductName, S.CategoryID, S.UnitPrice, S.Discontinued); -- detail of MERGE INTO example above MERGE INTO Production.Products AS P -- USING a source table or staging table as the table where the new values are coming from USING Production.ProductsStaging AS S -- match the source and target table rows using PdocutID ON P.ProductID = S.ProductID -- tells what to do if row exists in source table and exists in target table. In this case, tells it to UPDATE that row. WHEN MATCHED THEN UPDATE SET P.UnitPrice = S.UnitPrice, P.Discontinued = S. Discontinued -- tells what to do if row from source table does not exist in target table. In this case, tells it to INSERT that row. WHEN NOT MATCHED THEN INSERT (ProductName, CategoryID, UnitPrice, Dicsontinued) VALUES (S.ProductName, S.CategoryID, S.UnitPrice, S.Discontinued); -- SELECT INTO, copies result set from a query into a new table. New table will not have metadata, but you can add that metadata later using an ALTER statement(s). SELECT LastName, FirstName, CustomerID INTO newtable FROM oldtable WHERE customerID <= 25