Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Subquery returned more than 1 value..not permitted

Author  Topic 

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-10-13 : 16:41:13

I am receiving the "Subquery returned more than 1 value. This is not permitted..." error and I would really appreciate it if someone can point out how I should resolve this issue. 

--Update audit status to Delinquent if it is not scheduled within 2 weeks after new quarter
DECLARE @CurrentQDate smalldatetime --First day of the quarter
DECLARE @CurrentQuarter varchar(5)-- Q + [current quarter]
DECLARE @CurrentYear smallint -- Current year
SET @CurrentQuarter= 'Q' + CAST ((SELECT DATENAME(qq, GETDATE())) as varchar(5))
SET @CurrentYear = (SELECT DATENAME(yy, GETDATE()))
SET @CurrentQDate = (SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) ,0))
UPDATE tbl_Audit 
SET AuditStatus = 'Delinquent: Audit Not Scheduled'
WHERE DATEDIFF (dd,@CurrentQDate,GETDATE())>= 11
AND AuditStatus = 'Pending Date'
AND ScheduledQuarter = @CurrentQuarter
AND ScheduledYear = @CurrentYear


I need it to update ALL records, but it seems like SQL doesn't allow that. I have tried to narrow it down to one record using MAX (Audit_ID) and Having, but I still can't resolve it.

Can someone provide a suggestion (not code, I would like to resolve it myself)as to how to solve this problem?

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2011-10-13 : 17:04:26
Is there a trigger on the audit table? What is generating the "Subquery returned more than 1 value. This is not permitted..." error? I don't see any sub-queries in the code you posted.
Go to Top of Page

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-10-13 : 17:26:35
quote:
Originally posted by Lamprey

Is there a trigger on the audit table? What is generating the "Subquery returned more than 1 value. This is not permitted..." error? I don't see any sub-queries in the code you posted.


Yes, there is a trigger. It is used to record the change into a DelinquentAudit table

CREATE TRIGGER UnscheduledAuditInsert
ON tbl_Audit
FOR UPDATE
AS
If (SELECT AuditStatus FROM INSERTED) = 'Delinquent: Audit Not Scheduled'
BEGIN
	INSERT INTO tbl_DelinquentAudits (Audit_ID, StatusChangeDate, StatusType)
	SELECT a.Audit_ID, GETDATE(), 'Unscheduled Audit'
	FROM tbl_Audit a
	INNER JOIN inserted i
	ON a.Audit_ID = i.Audit_ID
	WHERE i.AuditStatus = 'Delinquent: Audit Not Scheduled'
END
GO
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-14 : 01:19:40
the problem is below line

If (SELECT AuditStatus FROM INSERTED) = 'Delinquent: Audit Not Scheduled'


here you're assuming that INSERTED will always have single row alone which is not tru for multiple (batch) updates. so you need to rewrite it as

If  (SELECT COUNT(*) FROM INSERTED WHERE AuditStatus = 'Delinquent: Audit Not Scheduled') >0
BEGIN
....


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-10-14 : 11:44:39
quote:
Originally posted by visakh16

the problem is below line

If (SELECT AuditStatus FROM INSERTED) = 'Delinquent: Audit Not Scheduled'


here you're assuming that INSERTED will always have single row alone which is not tru for multiple (batch) updates. so you need to rewrite it as

If  (SELECT COUNT(*) FROM INSERTED WHERE AuditStatus = 'Delinquent: Audit Not Scheduled') >0
BEGIN
....


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Thank you. It works, but for some reason the trigger is inserting two records for each update. I have tried using the DISTINCT and MAX functions but no luck.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-14 : 11:48:45
is Audit_ID primary key of tbl_Audit ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-10-14 : 12:27:30
quote:
Originally posted by visakh16

is Audit_ID primary key of tbl_Audit ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Yes, it is. I found that my other UPDATE trigger was the problem so I removed it for now. However, I've found that if there are multiple updates, the trigger is only inserting one record into the table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-14 : 12:30:43
nope. thats not true.provided the condition
AuditStatus = 'Delinquent: Audit Not Scheduled'
is satisfied it should insert all the records.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-10-14 : 12:58:04
quote:
Originally posted by visakh16

nope. thats not true.provided the condition
AuditStatus = 'Delinquent: Audit Not Scheduled'
is satisfied it should insert all the records.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




But if I run the update script, and three Audit records meets the criteria, then all three are updated. However the trigger only fires once.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-14 : 13:03:25
trigger only fires once but will process all the 3 records in one go.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-10-14 : 13:24:23
quote:
Originally posted by visakh16

trigger only fires once but will process all the 3 records in one go.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Right, but if Audit 1,2,and 3 are all updated, the trigger only inserts a record for Audit 1.I need it to insert records for 1,2, and 3.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-14 : 13:31:19
thats not true. Provided you did a set based update which affected 3 records all the 3 records will be available in INSERTED and would get inserted to tbl_DelinquentAudits provided they all have AuditStatus = 'Delinquent: Audit Not Scheduled'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tkepongo
Yak Posting Veteran

62 Posts
Posted - 2011-10-14 : 13:37:42
Oh wow, I forgot I added a MAX function in the trigger when I was troubleshooting it earlier, which caused it not to work properly. Thank you for your patience; I'm an idiot
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-10-14 : 13:39:49
no problem.
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site