Update with inner join sql 2008

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 12 years, 2 months ago.

Active 29 days ago. Viewed 1. I want to update a column in a table making a join on other table e. What is wrong here? Manjot Manjot Add a comment. Active Oldest Votes. Table2 AS t2 ON t1. Aaron Bertrand Aaron Bertrand k 36 36 gold badges silver badges bronze badges. Didn't work for me on MySQL. GeorgeRappel of course, probably won't work on many other platforms either.

The question is about SQL Server. Let's say multiple records from t1 referenced the same record from t2 so the join results in the same t2 record returned in multiple rows. In your first example, if you instead updated t2, would it update that record multiple times or just once? This detailed article will cover the following topics,.

First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see. If you try to update multiple columns from multiple tables, an error message will appear. Abe Miessler Abe Miessler GratuityYtodate" could not be bound.

That is not t-sql syntax. You could also simply remove the aliases from the set statement, it is assumed they are from the table you are updating. Try the following query. JiNish JiNish 7 7 bronze badges.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. The main problems in this plan are the hash join and sort. Both require a memory grant the hash join needs to build a hash table, and the sort needs room to store the rows while sorting progresses. Plan Explorer shows this query was granted MB:. This is quite a lot of server memory to dedicate to one query!

More to the point, this memory grant is fixed before execution starts based on row count and size estimates. This is known as a 'spill' and it can be a very slow operation. You would have to use Profiler to check, but I suspect the sort will spill to tempdb in this case.

It is also possible that the hash table spills too, but that is less clear-cut. Note that the memory reserved for this query is split between the hash table and sort, because they run concurrently. The Memory Fractions plan property shows the relative amount of the memory grant expected to be used by each operation. The sort is introduced by the query optimizer to ensure that rows arrive at the Clustered Index Update operator in clustered key order.

This promotes sequential access to the table, which is often much more efficient than random access. The hash join is a less obvious choice, because it's inputs are similar sizes to a first approximation, anyway.

Hash join is best where one input the one that builds the hash table is relatively small. In this case, the optimizer's costing model determines that hash join is the cheaper of the three options hash, merge, nested loops. The cost model does not always get it right. It tends to over-estimate the cost of parallel merge join, especially as the number of threads increases.

We can force a merge join with a query hint:. This produces a plan that does not require as much memory because merge join does not need a hash table :. The problematic sort is still there, because merge join only preserves the order of its join keys tempId, n but the clustered keys are tempId, n, sporder.