CODEWITHSUNDEEP
sqlsql-server
xMilos
xMilos

Reputation: 2018

MSsql update column from joint table

I have 2 tables ex. TABLE1 and TABLE2

TABLE1                TABLE2
ID | SIZE | VALUE     ID  | SIZE | SCORE 
1  | LOW  | 1.0       1   | MID  | 3232
2  | MID  | 3.0       2   | MID  | 2321
3  | HIGH | 5.0       3   | HIGH | 3232

what i want is to update TABLE2.SCORE so the values will be TABLE1.value column and the join to be SIZE.

   OUTPUT:
      ID  | SIZE | SCORE 
      1   | MID  | 3.0
      2   | MID  | 3.0
      3   | HIGH | 5.0

I tried: Update Table2 set SCORE=(select top(1) VALUE from TABLE1 join TABLE2 on table1.size=table2.size ) however this does not work I get this result:

 OUTPUT:
  ID  | SIZE | SCORE 
  1   | MID  | 3.0
  2   | MID  | 3.0
  3   | HIGH | 3.0 <---- wrong

Upvotes: 1

Views: 540

Answers (5)

Sreenu131
Sreenu131

Reputation: 2516

Try this

DECLARE @TABLE1  AS TABLE(ID INT , SIZE VARCHAR(10) , VALUE decimal(2,1))   
INSERT INTO @TABLE1
SELECT 1  , 'LOW'  , 1.0  UNION ALL    
SELECT 2  , 'MID'  , 3.0  UNION ALL    
SELECT 3  , 'HIGH' , 5.0      

DECLARE @TABLE2  AS TABLE(ID INT , SIZE VARCHAR(10) , SCORE INT)
INSERT INTO @TABLE2
SELECT 1   , 'MID'  , 3232 UNION ALL
SELECT 2   , 'MID'  , 2321 UNION ALL
SELECT 3   , 'HIGH' , 3232 


SELECT * FROM @TABLE2

UPDATE t2
SET SCORE=t1.VALUE
FROM @TABLE2 t2 inner join @TABLE1 t1 On t1.SIZE=t2.SIZE


SELECT ID,SIZE, CAST(SCORE AS DECIMAL(2,1)) AS SCORE

FROM @TABLE2

Demo result : http://rextester.com/VFF59681

Upvotes: 2

Lajos Arpad
Lajos Arpad

Reputation: 76583

You can achieve it like this:

update table2
set table2.SCORE = table1.VALUE
from table2
join table1
on table2.SIZE = table1.SIZE

However, to avoid problems, you will need to make sure table1.SIZE is unique.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You don't need to do the JOIN in subquery you can directly express it as :

update table2 
       set score = (select top (1) t1.score from table1 t1 where t1.size = table2.size);

Upvotes: 1

Chanukya
Chanukya

Reputation: 5893

update a
set a.score=b.score
from table2 a join table1 b on a.id=b.id

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can use a JOIN in the UPDATE:

update t2
    set t2.score = t1.score
    from table2 t2 join
         table1 t1
         on t2.size = t1.size;

You can also follow your pattern by using a correlated subquery:

update table2
    set t2.score = (select t1.score from table1 t1 where t1.size = table2.size);

There is no need for another JOIN in the subquery.

Upvotes: 2

Related Questions