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
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.
Just for your ans try this, select
top 1 in
update query when you are not sure about the number of
rows return by your
Update Trade set Reference = (Select top 1 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)
User contributions licensed under CC BY-SA 3.0