Update first value from multiple result join SQL Server 2000

Question

I have this query

UPDATE CCR_ARIZTIA 
SET HORA_ENTRADA_CLI = D1
FROM 
    CCR_ARIZTIA C
LEFT JOIN 
    TEMP_ALM_V_TEMP T ON ID_POLIGONO = POLIGONO AND T.L2 = C.L2
                       AND D1 > CONVERT(VARCHAR, GETDATE(), 23) AND D1 < GETDATE() 
WHERE 
    FECHA_PEDIDO = CONVERT(VARCHAR, GETDATE(), 23) 
    AND HORA_ENTRADA_CLI IS NULL 
    AND I4 = 222

It works fine, except when exists more than a D1 value that matches the join conditions. In this case the update sets the last value.

How can I prevent this? I want that the value of D1 be the first to appear.

I tried to use group by and order desc, but it seems that you can not use them in update statements.

Thank you.

EDIT:

When I run this query I have the the following results.

SELECT ID_CCR, MIN(D1) AS D1

FROM CCR_ARIZTIA C

LEFT JOIN TEMP_ALM_V_TEMP T ON ID_POLIGONO = POLIGONO AND T.L2 = C.L2
AND D1 > CONVERT(VARCHAR, GETDATE(), 23) AND D1 < GETDATE() 

WHERE FECHA_PEDIDO = CONVERT(VARCHAR, GETDATE(), 23) AND HORA_ENTRADA_CLI IS NULL AND I4 = 222

GROUP BY ID_CCR

ID_CCR  D1
4088    2014-11-17 09:21:19.000
4090    2014-11-17 07:20:16.000
4105    2014-11-17 13:23:00.000
4194    2014-11-17 12:53:52.000

This are the values I want to update in HORA_ENTRADA_CLI

Answer 1

I think this will do the job you want in Sql Server 2000:

UPDATE CCR_ARIZTIA 
SET HORA_ENTRADA_CLI = C.D1
FROM (SELECT ID_CCR, MIN(D1) AS D1
      FROM CCR_ARIZTIA C
      LEFT JOIN TEMP_ALM_V_TEMP T ON ID_POLIGONO = POLIGONO AND T.L2 = C.L2
                                      AND D1 > CONVERT(VARCHAR, GETDATE(), 23) AND D1 < GETDATE() 
      WHERE FECHA_PEDIDO = CONVERT(VARCHAR, GETDATE(), 23) AND HORA_ENTRADA_CLI IS NULL AND I4 = 222      
      GROUP BY ID_CCR) C
WHERE C.ID_CCR = CCR_ARIZTIA.ID_CCR

Answer 2

You should be able to do this with a cte.

Since I don't have ddl to work with I have to guess a little bit. You will need to change PrimaryKey to whatever makes sense to use a grouping column(s).

with MyUpdate as
(
    Select HORA_ENTRADA_CLI
        , MIN(D1)
    FROM CCR_ARIZTIA C
    LEFT JOIN TEMP_ALM_V_TEMP T ON ID_POLIGONO = POLIGONO 
        AND T.L2 = C.L2 AND D1 > cast(GETDATE() as DATE) 
        AND D1 < GETDATE() 
    WHERE FECHA_PEDIDO = cast(GETDATE() as DATE)
        AND HORA_ENTRADA_CLI IS NULL 
        AND I4 = 222
    group by [PrimaryKey]
)


UPDATE MyUpdate
SET HORA_ENTRADA_CLI = D1