-- creating the table CREATE TABLE dbo.StandardPrices( ProductID int , [Year] int , StandardPrice decimal(13,2) ) --creating test data DECLARE @index int SET @index = 0 WHILE @index < 50 BEGIN INSERT dbo.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 dbo.StandardPrices ORDER BY ProductID, [Year]
Let's try to use a normal update:
--first update attempt UPDATE dbo.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.
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.
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 dbo.StandardPrices SET StandardPrice = A.StandardPrice FROM ( SELECT ProductID , [Year] , StandardPrice FROM dbo.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 table's content after update SELECT * FROM dbo.StandardPrices ORDER BY ProductID , [Year]
No comments:
Post a Comment