Select 75% of records to rename, based on column sum

I have a scenario where I need to rename a value in one column, based on another column's total. Example table below with basic math, to express concept. I'd like to change the value in 'Condition' column to "Used" for the rows that make up 70% of the 'Revenue' column (which in this example would be 7 rows). The other 30% would be renamed to "New" (the remaining 3 rows). No other specific logic required.

I found that the approach mentioned here works for selecting the percentage of rows required

https://stackoverflow.com/questions/45153631/select-rows-whos-sum-value-80-of-the-total

I suppose I could create two temporary tables, rename the column fields in each respective table, and then join together. Curious if there is an easier way?

Current Table:

New Table:

You could do this with two updates. The first would update the entire table. The second would update the first 70%.

First we need sample data in a table. I used a table variable here but you would use your actual table.

declare @Something table
(
	Source char(1)
	, Condition varchar(10)
	, Revenue int
)

insert @Something values
('A', 'Old', 1)
, ('B', 'New', 1)
, ('C', 'Old', 1)
, ('D', 'New', 1)
, ('E', 'Old', 1)
, ('F', 'New', 1)
, ('G', 'Old', 1)
, ('H', 'New', 1)
, ('I', 'Old', 1)
, ('J', 'New', 1)

select *
from @Something;

Next simply update the entire table.

update @Something
set Condition = 'New';

Last step is to update the first 70%. An easy to do this is to use a cte to select the first 70% and then update the cte.

with Top70 as
(
	select top 70 percent *
	from @Something
	order by Source
)
update Top70
set Condition = 'Used';

Here is the final output.

select *
from @Something;

--EDIT--

Now understanding we need a running total you could do something like this.

select *
	, case when sum(Revenue) over(order by Source) > (sum(Revenue) over() * .7) then 'New' else 'Old' end
from @Something

You can select/mark the 70% and 30% records using this query :

with cte as (
  SELECT *, SUM(revenue) OVER(ORDER BY source) AS cumulative_revenue, SUM(revenue) OVER() as total
  FROM mytable t
)
select Source, iif((cumulative_revenue + 0.0) /total <= 0.7, 'Used', 'New') as Condition, revenue, cumulative_revenue,  (cumulative_revenue + 0.0) /total as perc
from cte

Demo here

You could chain a couple of CTEs to run the UPDATE

DROP TABLE IF EXISTS #t
CREATE TABLE #t([Source] VARCHAR(10), [Condition] VARCHAR(10), Revenue INT)
INSERT INTO #t([Source], [Condition], [Revenue])
values 
('A', 'Old', 1)
,('B', 'New', 1)
,('C', 'Old', 1)
,('D', 'New', 1)
,('E', 'Old', 1)
,('F', 'New', 1)
,('G', 'Old', 1)
,('H', 'New', 1)
,('I', 'Old', 1)
,('J', 'New', 1)
;WITH cte AS (
    SELECT *, SUM( Revenue) OVER (ORDER BY Source) ACC
    FROM #t
), cte2 as(
    SELECT MAX(acc)*1.  TotalRevenue   FROM cte
)
UPDATE cte 
SET Condition = CASE WHEN Acc / TotalRevenue <= .7 THEN 'Used' ELSE 'New' END 
FROM cte 
CROSS APPLY (SELECT TotalRevenue FROM cte2) ca

SELECT * FROM #t