Puzzled by Update ... From

• Ian-386680

  Mr or Mrs. 500

  Points: 509

  More actions

  May 2, 2007 at 9:55 am

  #178265

  I'm trying to understand how this piece of code I've been given to run actually works. It's using a FROM clause alongside an UPDATE. I think I understand the first part up to the 'from' clause.

              update item

  set sectn_dept_id=(select sectn_dept_id from sectn_dept

              where sectn_sc=item_sc and dept_id=0)

  from sectn_dept

  where item.sectn_dept_id=sectn_dept.sectn_dept_id

              and (sectn_dept.dept_id<>0 or item.sectn_dept_id=0 and item_id<>0);

  If I understand correctly, the first part of the operation runs like this :

  For each row in the 'item' table, the correlated query in parentheses is run, returning a value which is used to set the 'sectn_dept_id' for the current row in the item table.

  I'm puzzled by the 'from' clause. I'd understood that the 'From' clause in an Update statement was used to limit the set of values upon which the update is run. In the examples I have seen, the table specified in the 'from' clause has been the same as the table in the Update statement itself.  

  Is it possible to think of the 'from' clause as being a correlated Select statement, which is run first, returning a set of rows against which the update statement is then run? If this is so, are the returned values (from the 'from' clause) determined by the 'set' statement? So in this example, the 'from' clause would return a set of values for the 'sectn_dept_id' field.

  regards,

  Ian

   

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  May 2, 2007 at 1:59 pm

  #703919

  This is the way a JOIN is done in an UPDATE statement. The tables Item and Sectn_Dept are being joined to limit the values that get updated.

  In other words, the table Item is being updated based on values from the table Sectn_Dept.

  -SQLBill