CODEWITHSUNDEEP
sqlsql-server
Dan An
Dan An

Reputation: 424

SQL update more than one value

I'm writing a query to update a table(TableA) use the value from another table (TableB), table B returns more than one value against each row in TableA and the request is to update with the biggest return value in TableB.

I can achieve this by:

UPDATE TableA 
SET Value = (SELECT TOP 1 B.Value FROM dbo.TableB B
                WHERE TableA.Key = B.Key
                ORDER BY B.Value desc)

I'm not sure it is the best way to achieve the require, moreover I worry about the performance of the above query. Does it scan multiple times on Table B to find the value for each row of Table A?

I tried some query e.g. use

update tableA
set Value = TableB.Value
from (Table A left join Table B ... where ...)

I think the second one may have a better performance, but I cannot retrieve the biggest value, since it doesn't support "order by".

Join Table B twice and compare the B's values may solve the problem, but the perform could worse than the first query.

Any ideas?

** the real situation is complex than the example, e.g. tableA and tableB need to join on "....or....or...or...", will cost more.

Upvotes: 0

Views: 1449

Answers (2)

Bogdan Gavril MSFT
Bogdan Gavril MSFT

Reputation: 21448

The examples you gave use correlated suqueries, so for each row in A the sub-query will be executed. You can avoid a correlated subquery by using TableA twice in your query, something like

UPDATE TableA 
SELECT TableA.Col1 = t2.Col1, TableA.Col2 = TableB.Col
FROM TableA t2
INNER JOIN TableB ON ...

Upvotes: 1

John Woo
John Woo

Reputation: 263693

use INNER JOIN againts a subquery which gets the maximum value of tableB.

UPDATE  a
SET     a.Value = b.max_val
FROM    tableA a
        INNER JOIN 
        (
            SELECT  key, MAX(VALUE) max_val
            FROM    tableb
            GROUP   BY  key
        ) b ON a.key = b.key

Upvotes: 1

Related Questions