CODEWITHSUNDEEP
sqlsql-server
meysam motamedi
meysam motamedi

Reputation: 75

update 2 column using result from another select in sql

enter image description here

enter image description here

I need to select distinct "col1" in table 1 and update "col1" and its class "col1Class" in table2

this is the code i wrote but its not working:

UPDATE testing 
SET col1 = a.col , Class = a.Class
from testing inner join 
( select distinct col1 , col1Class As col,class
from TestAll 
)a

Upvotes: 0

Views: 63

Answers (3)

Mukund
Mukund

Reputation: 1689

;with cte(col1, colclass)
as
(
    select col1, colclass
    from table1
    group by col1,colclass
)

update t2
set col1 = c.col1, class = c.colClass
from table2 t2
inner join cte c on t2.col1 = c.col1

try this.

Upvotes: 0

NewSQL
NewSQL

Reputation: 181

 UPDATE B 
 SET B.CLASS=A.CLASS
 FROM TESTING B
 JOIN (SELECT DISTINCT COL1 AS COL,COL1CLASS AS CLASS FROM TESTALL)A
 ON B.COL1=A.COL

Upvotes: 1

cronos2546
cronos2546

Reputation: 1106

UPDATE testing 
SET col1 = A.col , Class = A.Class
FROM testing INNER JOIN 
(SELECT DISTINCT col1 , col1Class As col,class
from TestAll) 
as A 
ON testing.col1 = A.col AND testing.col1class = A.class

Upvotes: 0

Related Questions