Any easy way around a subquery that returns more than one value? 1

[Gunnien]

Hello,

I have one table [Productionreport_Recepi_Basis Template 2] that builds on a view called [Productionreport_Recepi_Basis Template 1] and it worked perfect until the 1st of november then the basis recepis wheren't updated any longer. I therefor will expand the last date with 365 days. I have tryed this update-query:

UPDATE [Productionreport_Recepi_Basis Template 2] SET [To date] = getdate()+365
WHERE [To date] = (SELECT Max([To Date])
FROM [Productionreport_Recepi_Basis Template 1]
WHERE [To Date] < getdate()
GROUP BY [Product number])

But when I try to execute this syntax I receive this errormessage:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

So can anyone out there tell me if there is an simple way to pass around the problem with more than one value?

I have tryed this also whitout any luck:


UPDATE [Productionreport_Recepi_Basis Template 2] SET [To date] = getdate()+365
WHERE MAX([To Date]) < getdate()
GROUP BY [Product number])

Regards

Gunnien

 

[swampBoogie]

UPDATE [Productionreport_Recepi_Basis Template 2] SET [To date] = getdate()+365
WHERE [To date] in (SELECT Max([To Date])
          FROM [Productionreport_Recepi_Basis Template 1]
          WHERE [To Date] < getdate()
          GROUP BY [Product number])

 

[2ni]

i had the same problem, the subquery return more than one result because two or more value in your [to date] column are equal

try to use this in your subquery :
select max(distinct [to date]) ....

 

[Gunnien]

Hi swampBoogie,

Thanks Swamp, so simpe and why didn't I think of the same?

 

[Gunnien]

Hi 2ni

The problem was that I intended to use my subquery bacause it was my intention to update all dates my subquery gave me.

But tahanks for your reply