CODEWITHSUNDEEP
sql-server
Kevin
Kevin

Reputation: 7309

How do I update a table with the max value of a different table?

I need to update col1 in table1 with the highest value in val in table2 that is lower than col2 in table1. I am trying this:


    update table1 set col1 = max(t2.val)
    from table2 t2 where t2.VAL < col2

I am getting this error: An aggregate may not appear in the set list of an UPDATE statement.

Here is sample data


Table1
Col1    Col2
2   null
3   null
4   null
6   null
7   null
8   null
9   null
10  null

Table2
Val
1
5

after I run the query I would want it to look like this:


Table1
Col1    Col2
2   1
3   1
4   1
6   5
7   5
8   5
9   5
10  5

Upvotes: 2

Views: 11582

Answers (2)

Royi Namir
Royi Namir

Reputation: 148524

UPDATE   t1     SET  
       modified = a.ddd from  t1 t  CROSS APPLY (SELECT MAX(t2.price) AS ddd  from t2 WHERE t2.price<t.val) a

lets initialize t1 (the modified field is the one that will have the max value from t2 but less than its val value)

enter image description here

this is your table1 (t2) with values ...

enter image description here

run the query : ...

UPDATE   t1     SET  
       modified = a.ddd from  t1 t  CROSS APPLY (SELECT MAX(t2.price) AS ddd  from t2 WHERE t2.price<t.val) a

enter image description here

edit

for your values here is the result :

enter image description here

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

UPDATE t1
    SET col2 = (SELECT MAX(Val) FROM dbo.Table2 WHERE Val < t1.col1)
    FROM dbo.Table1 AS t1;

Upvotes: 3

Related Questions