Update on Varchar column of a table that has > 10 millions rows
declare @var  varchar(max) = (select metrcikey from metricdim where metrciname ='XYZ')

Update
    fopty
set
    Metrickey = metrickey+','+@var
from
    optyfact opty
inner join
    optydim dim on opty.optyid = dim.optyid
inner join
    geodim geo on geo.atukey = opty.atukey
inner join
    agreementdim ag on opty.optyid = ag.optyid
inner join
    account acc on acc.optyid = acc.optyid
where
    dim.optytype= 'ABC'
    and geo.atukey =145
    and ag.agreementtype ='Sold'
    and acc.accountteamManager  ='XXX'

Metrickey = Metrickey + @var

CREATE TABLE #q(rn INT IDENTITY(1,1) PRIMARY KEY, optyid INT);

DECLARE
  @rc    INT,
  @step  INT = 1,
  @chunk INT = 100000;

INSERT #q(optyid) SELECT DISTINCT opty.optyid
FROM dbo.optyfact AS opty
INNER JOIN dbo.optydim AS dim      ON opty.optyid = dim.optyid
INNER JOIN dbo.geodim AS geo       ON geo.atukey = opty.atukey
INNER JOIN dbo.agreementdim AS ag  ON opty.optyid = ag.optyid
INNER JOIN dbo.account AS acc      ON acc.optyid = acc.optyid
WHERE
  dim.optytype = 'ABC';
  AND geo.atukey = 145
  AND ag.agreementtype = 'Sold'
  AND acc.accountteamManager = 'XXX';

SET @rc = @@ROWCOUNT;

WHILE @step <= ((@rc / @chunk) + 1)
BEGIN
    BEGIN TRANSACTION;

    UPDATE o SET MetricKey += ',' + @var
    FROM dbo.optyfact AS o
    INNER JOIN #q AS q ON o.optyid = q.optyid
    WHERE q.rn BETWEEN (((@step-1)*@chunk)+1) AND (@step*@chunk);

    COMMIT TRANSACTION;
    CHECKPOINT;
    SET @step += 1;
END

DROP TABLE #q;