CODEWITHSUNDEEP
sqlsql-servert-sql
SmartestVEGA
SmartestVEGA

Reputation: 8889

update same rows

I have a table like below: Current

    Column1 Column2 column3
   -------------------------
    abc       cat1     1 
    efg       cat1     3
    hij       cat1     2
    klm       cat2     1
    nop       car2     2
    qrs       cat2     3

I want to update column1 where all rows matching cat1 from rows matching cat2 , considering rows will be updated for id which matches column 3 ids

Expected

Column1 Column2 column3
-------------------------
klm       cat1     1 
qrs       cat1     3
nop       cat1     2
klm       cat2     1
nop       car2     2
qrs       cat2     3

Upvotes: 0

Views: 48

Answers (2)

Praveen ND
Praveen ND

Reputation: 560

Please try with below code:

    DECLARE @Table TABLE
    (Column1 VARCHAR(100),Column2 VARCHAR(100), Column3 INT)

     INSERT INTO @Table VALUES
     ('abc','cat1',1),
     ('efg','cat1',3),
     ('hij','cat1',2),
     ('klm','cat2',1),
     ('nop','cat2',2),
     ('qrs','cat2',3)

    UPDATE 
    t 
    SET t.Column1 = d.Column1
    FROM @Table t INNER JOIN @Table d 
    ON t.Column3 = d.Column3
    WHERE d.Column2 ='cat2'

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

Use below query :

 CREATE TABLE #Table (Column1 VARCHAR(100),Column2 VARCHAR(100), Column3    
 VARCHAR(100))

 INSERT INTO #Table ( Column1  ,    Column2  , Column3  )
 SELECT 'abc','cat1',1 UNION ALL
 SELECT 'efg','cat1',3 UNION ALL
 SELECT 'hij','cat1',2 UNION ALL
 SELECT 'klm','cat2',1 UNION ALL
 SELECT 'nop','cat2',2 UNION ALL
 SELECT 'qrs','cat2',3


 UPDATE #Table SET Column1 = A.Column1
 FROM
 (
   SELECT Column1 , Column3 
   FROM #Table
   WHERE Column2 = 'cat2'
 )A WHERE #Table.Column2 = 'cat1' AND  A.Column3 = #Table.Column3

 SELECT * FROM #Table

http://rextester.com/QYHLTE44670

Upvotes: 1

Related Questions