Getting error: SqlException (0x80131904):Subquery returned more than 1 value - why?

Error when running my query :

System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

This is my code:

UPDATE Trade
SET Reference = (SELECT DISTINCT temp.MainRecordNo
                 FROM temp
                 WHERE temp.SubRecordNo = Trade.TradeNo 
                   AND temp.LinkType = 'ATPD'
                   AND LinkStatus = 'A'
                   AND ISNULL(Trade.Reference, '') <> Temp.MainRecordNo)
WHERE EXISTS (SELECT DISTINCT Temp.MainRecordNo
              FROM Temp
              WHERE Temp.SubRecordNo = Trade.TradeNo
                AND Temp.LinkType = 'ATPD'
                AND LinkStatus = 'A'
                AND ISNULL(Trade.CstpReference, '') <> Temp.MainRecordNo)

How to fix it?

First, you should rephrase this using a JOIN:

UPDATE t
    set Reference = temp.MainRecordNo
FROM Trade t JOIN
     temp
     ON Temp.SubRecordNo = t.TradeNo
WHERE temp.LinkType = 'ATPD' AND
      temp.LinkStatus = 'A' AND  -- Guessing this comes from temp
      (t.CstpReference IS NULL OR t.CstpReference <> Temp.MainRecordNo);

This will solve your immediate problem. You have another problem, which is that multiple records in temp match a single record in Trade. It is entirely unclear how to resolve that. This will update with a value from an arbitrary matching row.