34

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

Improve this question

1 Answer 1

Reset to default
48

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 ...
Improve this answer
4
  • 4
    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.
    – Kjell
    Commented Nov 25, 2015 at 16:19
  • 1
    In SQL Server, I had to replace "UPDATE some_table" with "UPDATE st"
    – Enric Naval
    Commented Jun 18, 2020 at 9:08
  • 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.
    – RaRdEvA
    Commented Jul 28, 2020 at 16:37
  • I didn't find the need for the alias. UPDATE some_table Set ... FROM some_table CROSS APPLY WHERE ...
    – Craig
    Commented Nov 1, 2021 at 18:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.