Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using cross apply in update statement

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

like image 398
Guillermo Gutiérrez Avatar asked Sep 20 '11 22:09

Guillermo Gutiérrez


People also ask

What is a cross apply and how do you use this?

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.

When to use cross Apply vs 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.

Is Cross apply same as inner join?

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.

Can you use a case statement in an update?

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.


1 Answers

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 ... 
like image 110
Guillermo Gutiérrez Avatar answered Oct 05 '22 10:10

Guillermo Gutiérrez