November 10, 2014 at 12:24 pm
Hi All,
I would appreciate some help with an alternative syntax for the SQL Statement below:
UPDATE [dbo].[TableA]
SET
sysReturnDate_Current =
CASE WHEN Frequency = 'Quarterly' AND ReturnDate IS NOT NULL THEN
(SELECT DATEADD(qq,1,ReturnDate)
FROM [dbo].[TableA]
WHERE [CurrentRecord] = 1) END
The statement is used to update another field with another column within the same table, if the fields:
Frequency ='Quarterly' AND
ReturnDate IS NOT NULL.
However, there could be many rows of records in the table with the field [CurrentRecord] = 1. This causes the error to be generated:
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.
I could make a variable and add the key column and do this one at a time.
But if you could suggest a set-based approach that would be great.
November 10, 2014 at 12:43 pm
Try:
UPDATE dbo.TableA
SET sysReturndate_Current = DATEADD(qq, 1, ReturnDate)
WHERE CurrentRecord = 1 AND Frequency = 'Quarterly' AND ReturnDate IS NOT NULL;
Gerald Britton, Pluralsight courses
November 11, 2014 at 3:20 am
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply