更新相同的行

Question

我有一个表像下面： 当前

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

我想更新COLUMN1，即当匹配的匹配CAT2，考虑排又一排CAT1所有行会为ID更新它匹配列3个IDS

预计

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

Answer 1

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

Answer 2

请下面的代码尝试：

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'