Update records based on other records in same table?

Transact-SQL
1 
DECLARE @test AS TABLE
(
CustID VARCHAR (50),
Period VARCHAR (2),
Yearno VARCHAR (4),
Revision VARCHAR (2),
Value DECIMAL (15, 2)
)

INSERT INTO @test
VALUES 
(
'FIN_CC1500-12-106-100-100', '01', '2018', '01', '10.24'
)
INSERT INTO @test
VALUES 
(
'FIN_CC1500-12-106-100-100', '02', '2018', '01', '10.24'
) 
INSERT INTO @test
VALUES 
(
'FIN_CC1500-12-106-100-100', '03', '2018', '01', '10.24'
) 
INSERT INTO @test
VALUES 
(
'FIN_CC1500-12-106-100-100', '01', '2018', '02', '-10.24'
)
INSERT INTO @test
VALUES 
(
'FIN_CC1500-12-106-100-100', '02', '2018', '02', '-10.24'
) 
INSERT INTO @test
VALUES 
(
'FIN_CC1500-12-106-100-100', '03', '2018', '02', '-10.24'
) 
SELECT * FROM @test

In the above example, I would like to update the values of the records with revision 2 with the corresponding values from revision 1.

Currently, all three records have a value of -10.24; after the update they will have a value of 10.24.

This is a small sample; the real world problem I have involves 21,000 records.

Thanks in advance!

2 
 DECLARE @test AS table
   (
      CustID   varchar(50)
    , Period   varchar(2)
    , Yearno   varchar(4)
    , Revision varchar(2)
    , Value    decimal(15, 2)
   ) ;

INSERT INTO @test
VALUES
(
   'FIN_CC1500-12-106-100-100'
 , '01'
 , '2018'
 , '01'
 , '10.24'
),(
   'FIN_CC1500-12-106-100-100'
 , '02'
 , '2018'
 , '01'
 , '10.24'
),(
   'FIN_CC1500-12-106-100-100'
 , '03'
 , '2018'
 , '01'
 , '10.24'
),(
   'FIN_CC1500-12-106-100-100'
 , '01'
 , '2018'
 , '02'
 , '-10.24'
),(
   'FIN_CC1500-12-106-100-100'
 , '02'
 , '2018'
 , '02'
 , '-10.24'
),(
   'FIN_CC1500-12-106-100-100'
 , '03'
 , '2018'
 , '02'
 , '-10.24'
) ;

SELECT
   CustID
 , Period
 , Yearno
 , Revision
 , Value
FROM
   @test ;

UPDATE t2 
SET [Value] = t1.Value
FROM @test t2
JOIN @test t1 
ON t2.CustID = t1.CustID
AND t2.Yearno = t1.Yearno
AND t2.Period = t1.Period
WHERE
   t2.Revision='02'
AND
   t1.Revision='01';

SELECT
   CustID
 , Period
 , Yearno
 , Revision
 , Value
FROM
   @test ;

image

1 Like
3

Very good; many thanks.