October 5, 2009 at 3:18 am
how to update data table form data selected from another table ?
i have two tables, i need to update some records in table 1 from data in table 2
How ??
October 5, 2009 at 4:19 am
You can use an UPDATE statement with a Join.
UPDATE A SET myCol=B.myCol
FROM myTable A
INNER JOIN myOtherTable B ON A.ID=B.ID
Post your two table defintions for more specific code
October 6, 2009 at 10:21 am
Example from SQL Server 2008 books online:
http://msdn.microsoft.com/en-us/library/ms177523.aspx
C. Using the UPDATE statement with information from another table
The following example modifies the SalesYTD column in the SalesPerson table to reflect the most recent sales recorded in the SalesOrderHeader table.
USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
ON sp.SalesPersonID = so.SalesPersonID
AND so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID =
sp.SalesPersonID);
Naveen Abraham
October 7, 2009 at 1:53 am
See also http://pratchev.blogspot.com/2008/03/updates-with-cte.html. This is my favourite for SQL 2005...:-)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply