Menu
Is it possible to use the cross apply clause in the from part of an update statement, in SQL Server 2005?
398
CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn't contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN.
In simple terms, a join relies on self-sufficient sets of data, i.e. sets should not depend on each other. On the other hand, CROSS APPLY is only based on one predefined set and can be used with another separately created set. A worked example should help with understanding this difference.
CROSS APPLY is similar to the INNER JOIN but it is used when you want to specify some more complex rules about the number or the order in the JOIN.
The CASE expression allows a statement to return one of several possible results, depending on which of several condition tests evaluates to TRUE. You must include at least one WHEN clause within the CASE expression; subsequent WHEN clauses and the ELSE clause are optional.
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 some_table 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 ... If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With