- IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.
Saturday, September 15, 2007
Updating Data With Values From The Same Table
I have to deal with cases in which I have to update data from a table with values from the same table across records, a simple examle I can scatch is the one of Standard Prices, which are normally valid during a year. For example if I would have to reset the Standard Prices from a year to the ones from the previous year, how this can be done? Actually that's pretty sample, for exemplification purposes I will create a table having only 3 columns, ProductID, Year and StandardPrice: --creating StandardPrices table CREATE TABLE StandardPrices( ProductID int , [Year] int , StandardPrice decimal(13,2) ) We need for it some sample data: --creating test data DECLARE @index int SET @index = 0 WHILE @index < 50 BEGIN INSERT StandardPrices(ProductID, [Year], StandardPrice) VALUES (@index % 25+1, 2000 + (@index/25), 100 * RAND()) SET @index = @index + 1 END Let's see the content, the RAND() function allows me to assign random prices to each product. As can be seen there are 25 products with Standard Prices for the Years 2000 and 2001. --testing StandardPrices table's content SELECT * FROM StandardPrices ORDER BY ProductID, [Year] Let's try to use a normal update: --first update attempt UPDATE StandardPrices SET StandardPrice = A.StandardPrice FROM StandardPrices A WHERE StandardPrices.ProductID = A.ProductID AND StandardPrices.[Year] = 2001 AND A.[Year] = 2000 Ups, I got an error (SQL Server 2005): Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "StandardPrices.ProductID" could not be bound. Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "StandardPrices.Year" could not be bound. SQL Server 2000: Server: Msg 107, Level 16, State 3, Line 1 The column prefix 'StandardPrices' does not match with a table name oralias name used in the query. Server: Msg 107, Level 16, State 1, Line 1 The column prefix 'StandardPrices' does not match with a table name oralias name used in the query. It seems we can't use the same table in updates of this type, but we can use a subquery or view which refers to the same table, so we can write: --the update UPDATE StandardPrices SET StandardPrice = A.StandardPrice FROM ( SELECT ProductID , [Year] , StandardPrice FROM StandardPrices WHERE [Year] = 2000 ) A WHERE StandardPrices.ProductID = A.ProductID AND StandardPrices.[Year] = 2001 Now there are no errors, 25 records updated, let's see the output: --testing StandardPrices table's content after update SELECT * FROM StandardPrices ORDER BY ProductID, [Year] That's all folks!