First version:

UPDATE [MOVEMENT]
SET [VAL] = [QTY] * ABS(MR.VAL / MR.QTY)
FROM [MOVEMENT] MR
WHERE --SOME CRITERIA
      [REF] = MR.AREF
AND [VAL] = 0

It does NOT complain and it seems all right, and runs without a problem, but since I selected the records with INNER JOIN and know that there are records that must be updated, we must find another way of updating those records. The problem is our table is referring to itself and the structure is identical, field names normally cause ambiguity problem in such queries.

Second version (fully qualified names):

UPDATE [MOVEMENT]
SET [MOVEMENT].[VAL] = [MOVEMENT].QTY * ABS(MR.VAL / MR.QTY)
FROM [MOVEMENT] MR
WHERE -- SOME CRITERIA
      [MOVEMENT].REF = MR.AREF
AND [MOVEMENT].[VAL] = 0

It will complain like this:

“The column prefix ‘MOVEMENT’ does not match with a table name or alias name used in the query.”

Third version (sub-query) is the right solution to get around this problem and deceive MS SQL Server 2000:

UPDATE [MOVEMENT]
SET [VAL] = [QTY] * Q.C1
FROM ( -- ILLUSION OF A SUBQUERY PROVIDING DIFFERENT COLUMN NAMES
    select MR.AREF AS AREF2
    , ABS(MR.VAL / MR.QTY) AS C1
    FROM [MOVEMENT] MR
    ) Q
WHERE --CRITERIA
      [REF] = Q.AREF2
AND [VAL] = 0
Tags: ,
Categories: