Update From

Hello.

I am a little lost... in the use of update ... from ...

Let us assume I have something like this:

UPDATE parts SET id_geometry = b.id_geometry_duplicate
FROM
parts a
INNER JOIN fn_duplicate_parts(@id_project) b ON a.id_part = b.id_part_org

How does this query know which rows it needs to update? I can see no relationsship between the tables parts and b (which by accident is also parts). I only want to update those rows in parts where id_part is in b(as id_part_org). I guess it is my lack of knowledge, but really I cannot see the logic in this.

Could anyone clarify?

Thanks in advance,

Lasse Johansen

I have never heard of the UPDATE FROM query before, but am very glad to have stumbled on it here! I think the way it works (someone correct me if i'm wrong!) is like this:

UPDATE parts
SET id_geometry = b.id_geometry_duplicate
FROM parts a INNER JOIN atable b ON a.id_part = b.id_part_org

your joining PARTS A to ATABLE B using your ID_PART and ID_PART_ORG and are left with a reuslt set of PARTS and ATABLE. the UPDATE then uses this result set to determine what rows in PARTS gets updated with information from ATABLE.

Regards,

Alaistair

My problem is only the last step, not the join. How can it actually see which rows in parts are to be updated?

The last part, FROM x INNER JOIN Y, gives a result set that basically acts as a giant WHERE clause that also provides the update with the necessary criteria

Alaistair

And that is what I cannot see. How does it know which rows? It does not have any joins to make references. This is the example from Microsoft:

C. Use the UPDATE statement using information from another table
This example modifies the ytd_sales column in the titles table to reflect the most recent sales recorded in the sales table.

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

Taking a look at the last part:
FROM titles, sales
WHERE titles.title_id = sales.title_id
AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

This is simply a JOIN which gives a result set. Where does this result set reference the table titles in:

UPDATE titles SET ytd_sales = etc etc

?????

I am lost. I guess I will become a mechanic instead of a computer programmer.

What I really wanted to say with this is: If what you say is true(that it act as a big where clause), how can it know on which column to make the WHERE(the relation) based upon.... Automatically by primary key? What if the table do not have a primary key? What if - in the microsoft example - the table titles did not appear in the FROM clause, but came from something else.... This makes the explanation flood :-(

If you have SQL Query Analyzer you enter you query and from the Query menu select estimated execution plan. this will show you exactly what the SQL engine will do to execute you query.