31

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

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116

1 Answers1

45

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 ...
JonH
  • 32,732
  • 12
  • 87
  • 145
Guillermo Gutiérrez
  • 17,273
  • 17
  • 89
  • 116
  • 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 Nov 25 '15 at 16:19
  • 1
    In SQL Server, I had to replace "UPDATE some_table" with "UPDATE st" – Enric Naval Jun 18 '20 at 09: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 Jul 28 '20 at 16:37
  • I didn't find the need for the alias. UPDATE some_table Set ... FROM some_table CROSS APPLY WHERE ... – Craig Nov 01 '21 at 18:47