Query results vary in SQL2000 and SQL2005

Hot Topic

Group By
Capturing Execution Results
Databases. SQL, .NET with VB 2k5 xpressss
Enterprise Manager for SQL server 2005
Summarizing members with the same name across dimensions
SQL Express Advanced with Sharepoint Services.
problem with custom assembly
Problem loading XML Files
Accessing (writing into) Terdata by ado.net and SSIS
Help - Rebuild System Databases on a clustered instance

SQL Server

String Collection problems with Script method??
Certain numeric fields not read from the Excel file when using a Excel file source.
Blank pages in report preview
@@ERROR logic not working
Frustrated with Nested Groups/Lists subtotals
Checking to see if a record exists and if so update else insert
Change Color of Textbox Depending on Value
SQL Server Setup Unicode
Too long text and sorting does not work.
make a stored procedure

Query results vary in SQL2000 and SQL2005

I have been executing the below Query in SQL2005. But the results vary when executed in SQL2000. Any knows why and what is the solution.

In SQL2005 it updates the first record from #Temp_Customer_Targets into #Temp_Customer_IQ but in SQL2000 it updates the last record.

Begin tran

Create Table #Temp_Customer_IQ(
Customer_Id T_ID,
Customer_Upper_Commission_Target T_Amount NULL
)

Create Table #Temp_Customer_Targets(
Customer_Id T_ID,
Calculated_Upper_Target T_Amount
)

Insert Into #Temp_Customer_IQ Values (2000000132,NULL)

Insert Into #Temp_Customer_Targets Values (2000000132,0.99)
Insert Into #Temp_Customer_Targets Values (2000000132,1.80)
Insert Into #Temp_Customer_Targets Values (2000000132,1.62)

Update #Temp_Customer_IQ
SET Customer_Upper_Commission_Target = Calculated_Upper_Target
FROM #Temp_Customer_IQ TCI
INNER JOIN #Temp_Customer_Targets TCT
ON TCI.Customer_Id = TCT.Customer_Id

Select * From #Temp_Customer_IQ

Rollback tran




Answer this question

Query results vary in SQL2000 and SQL2005

  • NetProgrammer

    Your UPDATE statement has non-deterministic behavior and this is a problem with the TSQL extension which is the one that allows FROM clause in the UPDATE statement. You should avoid it as far as possible and use the ANSI SQL syntax for UPDATE statement. So you need to do one of the following depending on your requirements:
     
    UPDATE #Temp_Customer_IQ
    SET Customer_Upper_Commission_Target = (
    SELECT MIN(c.Calculated_Upper_Target)
    FROM #Temp_Customer_Targets as c
    WHERE c.Customer_Id = #Temp_Customer_IQ.Customer_Id
    )
     
    -- or
    UPDATE #Temp_Customer_IQ
    SET Customer_Upper_Commission_Target = (
    SELECT MAX(c.Calculated_Upper_Target)
    FROM #Temp_Customer_Targets as c
    WHERE c.Customer_Id = #Temp_Customer_IQ.Customer_Id
    )
     
    -- or
    UPDATE #Temp_Customer_IQ
    SET Customer_Upper_Commission_Target = (
    SELECT SUM(c.Calculated_Upper_Target)
    FROM #Temp_Customer_Targets as c
    WHERE c.Customer_Id = #Temp_Customer_IQ.Customer_Id
    )
     
    See UPDATE statement topic in Books Online for more details on the non-deterministic behavior of the proprietary syntax.
     


  • jk_uk2

    Thanks for the input. Would do the necessary changes

  • Alan Adams

    You shouldn't rely on SQL Server choosing any one particular value. That is, the update should only return ONE row for each matched row. In this case, you have multiple rows returning, and hence you shouldn't trust that the same value (1st, or 3rd) will always be returned.


  • alienated

    Thanks for the input. Would do the necessary changes


  • Query results vary in SQL2000 and SQL2005
Answer this question