Change column values based on another column in same table

Question

I have the table

+---------------------+
| ID | Code | Amount -|
+---------------------+
| 1  | 101  | 1.2     | 
| 1  | 102  | 1.3     |
| 1  | 103  | 1.3     |
| 1  | 104  | 1.4     |
| 1  | 105  | 1.2     |
| 2  | 101  | 1.5     |
| 2  | 102  | 1.4     |
| 2  | 103  | 1.3     |
| 2  | 104  | 1.1     |
| 2  | 105  | 1.0     |
+---------------------+

What I am trying to do is change the amount column for each ID which is not the code 101 to the value in amount from code 101

So my output should be like this.

+---------------------+
| ID | Code | Amount -|
+---------------------+
| 1  | 101  | 1.2     | 
| 1  | 102  | 1.2     |
| 1  | 103  | 1.2     |
| 1  | 104  | 1.2     |
| 1  | 105  | 1.2     |
| 2  | 101  | 1.5     |
| 2  | 102  | 1.5     |
| 2  | 103  | 1.5     |
| 2  | 104  | 1.5     |
| 2  | 105  | 1.5     |
+---------------------+

This is clearly a simplified table to show what I need as the row count today is over 100,000 but will change everyday.

I have tried to use a cursor but it is very slow. Is there anyway to do this?

Thanks

Answer 1

update t
set t.amount = t2.amount
from your_table t
join 
(
  select id, min(amount) as amont
  from your_table
  where code = 101
  group by id
) t2 on t1.id = t2.id
where t.code <> 101

Answer 2

This would do the trick:

DECLARE @t table(ID int, Code int, Amount decimal(6,1))

INSERT @t values
(1,101,1.2),(1,102,1.3),
(1,103,1.3),(1,104,1.4),
(1,105,1.2),(2,101,1.5),
(2,102,1.4),(2,103,1.3),
(2,104,1.1),(2,105,1.0)

;WITH CTE AS
(
  SELECT 
    min(CASE WHEN Code = 101 THEN amount end) 
      over (partition by ID) newAmount,
    Code,
    Amount
  FROM @t
)
UPDATE CTE 
SET Amount = newAmount 
WHERE 
  code <> 101
  AND newAmount is not NULL


SELECT * FROM @t

Result:

ID  Code  Amount
1   101   1.2
1   102   1.2
1   103   1.2
1   104   1.2
1   105   1.2
2   101   1.5
2   102   1.5
2   103   1.5
2   104   1.5
2   105   1.5

Answer 3

Update YourTable
Set Amount = (select top 1 a.amount from yourtable a where a.id = YourTable.id and a.code = 101)

EDIT:

Check this

select *,(select top 1 a.amount from yourtable a where a.id = YourTable.id and a.code = 101) as [your update value]
from YourTable

Answer 4

Use a sub-query to get the 101 amount for the id:

update tablename t1 set amount = (select max(amount) from tablename t2
                                  where t1.ID = t2.id
                                    and t2.code = 101)
where t1.code <> 101;

No need to update 101 rows, keep them out of the transaction (where t1.code <> 101).

MAX(amount) is there to make sure only one row returned from sub-query.

Answer 5

You can use UPDATE FROM like this

UPDATE Tbl1
SET Amount = Tbl2.Amount
FROM Tbl1
INNER JOIN
(
SELECT ID,Code,Amount
FROM Tbl1
WHERE Code = 101
)Tbl2
ON Tbl1.ID = Tbl2.ID
WHERE Tbl1.Code <> 101;

Edit

If there can be multiple amount values for the same code and ID, GROUP BY can be used like this.

UPDATE Tbl1
SET Amount = Tbl2.Amount
FROM Tbl1
INNER JOIN
(
SELECT ID,MAX(Amount) as Amount
FROM Tbl1
WHERE Code = 101
GROUP BY ID
)Tbl2
ON Tbl1.ID = Tbl2.ID
WHERE Tbl1.Code <> 101;