WITH T AS ( SELECT NULL COL1, '가' COL2, 'A' COL3 FROM DUAL UNION ALL SELECT NULL COL1, '가' COL2, 'A' COL3 FROM DUAL UNION ALL SELECT NULL COL1, '가' COL2, 'A' COL3 FROM DUAL UNION ALL SELECT NULL COL1, '나' COL2, 'A' COL3 FROM DUAL UNION ALL SELECT NULL COL1, '나' COL2, 'A' COL3 FROM DUAL UNION ALL SELECT NULL COL1, '나' COL2, 'A' COL3 FROM DUAL UNION ALL SELECT NULL COL1, '다' COL2, 'A' COL3 FROM DUAL UNION ALL SELECT NULL COL1, '다' COL2, 'A' COL3 FROM DUAL UNION ALL SELECT 1 COL1, '가' COL2, 'B' COL3 FROM DUAL UNION ALL SELECT 1 COL1, '가' COL2, 'B' COL3 FROM DUAL UNION ALL SELECT 2 COL1, '나' COL2, 'B' COL3 FROM DUAL UNION ALL SELECT 2 COL1, '나' COL2, 'B' COL3 FROM DUAL UNION ALL SELECT 3 COL1, '다' COL2, 'B' COL3 FROM DUAL UNION ALL SELECT 3 COL1, '다' COL2, 'B' COL3 FROM DUAL UNION ALL SELECT 3 COL1, '다' COL2, 'B' COL3 FROM DUAL UNION ALL SELECT 3 COL1, '다' COL2, 'B' COL3 FROM DUAL ) SELECT B.COL1, A.COL2, A.COL3 FROM T A , (SELECT DISTINCT * FROM T WHERE COL3='B') B WHERE A.COL2= B.COL2 ORDER BY A.COL3, B.COL1 ;
밑에서 4번째행 3, 나, B 은 오타로 간주하였습니다.
-- Oracle UPDATE t a SET column1 = (SELECT column1 FROM t WHERE column2 = a.column2 AND column3 = 'B' AND ROWNUM = 1 ) WHERE column3 = 'A' ;
-- MSSQL UPDATE a SET a.column1 = b.column1 FROM t a INNER JOIN (SELECT column2 , MIN(column1) column1 FROM t WHERE column3 = 'B' GROUP BY column2 ) b ON a.column2 = b.column2 WHERE a.column3 = 'A' ;