Using cross apply in update statement

Question

Is it possible to use the cross apply clause in the from part of an update statement, in SQL Server 2005?

Scorpius

Answer 1

You where right, Albert. I made some tests and found that it's possible, indeed. The use is the same as in a SELECT statement. For example:

UPDATE 
    st
SET 
    some_row = A.another_row,
    some_row2 = A.another_row/2
FROM 
    some_table st
CROSS APPLY
    (SELECT TOP 1 another_row FROM another_table at WHERE at.shared_id=st.shared_id) AS A
WHERE ...

Scorpius

The above query gave me the error "Incorrect syntax near ')'." It took me a little while to figure out why so I just want to add the solution here in case anyone runs into it. Adding an "as" after the cross apply select statement seemed to fix it. – Unbuckle

In SQL Server, I had to replace "UPDATE some_table" with "UPDATE st" – Flapjack

One benefit of using cross apply is that you can use "top" and "sort"; that enables you to perform the update in a specific order you want. – Reunion

I didn't find the need for the alias. UPDATE some_table Set ... FROM some_table CROSS APPLY WHERE ... – Gulosity