July 2, 2012 at 9:35 am
This is not rocket science, and honestly I don't understand why I've suddenly started to get this error, but starting today I have. Several times, interestingly enough using the same table (Providers). Here's an example of a simple update I'm trying to do, which causes the error to come up:
UPDATE
Providers
SET ContractIsCurrent = 0
WHERE ProviderNumber IN (116, 101)
Provider is just one table. For completeness sake, here's Providers' definition:
CREATE TABLE Providers(
ProviderNumber smallint NOT NULL,
Facility smallint NOT NULL,
Description varchar(100) NOT NULL,
DisplayName varchar(100) NULL,
ProviderStreetAddress varchar(50) NULL,
ProviderCity varchar(25) NULL,
ProviderState varchar(2) NULL,
ProviderZip varchar(10) NULL,
StaffCode smallint NULL,
ProviderPhone varchar(15) NULL,
MuniCode varchar(6) NULL,
ContractName varchar(100) NULL,
Contact smallint NULL,
AlternateContact smallint NULL,
FaxNumber varchar(15) NULL,
NativeAmerLanguage varchar(50) NULL,
OtherLanguage varchar(50) NULL,
ServicesProvidedMask int NULL,
EnglishSpanish tinyint NULL,
VoucherParticipant bit NOT NULL,
ContractIsCurrent bit NOT NULL,
AgeUnder17 bit NOT NULL,
Age18To25 bit NOT NULL,
Age26To54 bit NOT NULL,
Age55AndOlder bit NOT NULL,
CulturalService bit NOT NULL,
GenderService bit NOT NULL,
GayLesbianService bit NOT NULL,
AgeService bit NOT NULL,
OtherMHServices varchar(100) NULL,
OtherPopulations varchar(100) NULL,
OtherSubstances varchar(100) NULL,
OtherServices varchar(100) NULL,
Comments varchar(255) NULL,
SpecialContract bit NOT NULL,
ApplyServiceEntryLimits tinyint NULL,
DebitLimit money NULL,
FaithBased bit NULL,
AcceptingReferrals bit NULL,
ApplyOutcomesReportingLimits tinyint NULL,
CONSTRAINT aaaaaProviders_PK PRIMARY KEY NONCLUSTERED
(
ProviderNumber ASC,
Facility ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON PRIMARY
) ON PRIMARY
When I run that UPDATE query, I get the following error message:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Honestly, I don't see what's wrong. There are only 2 rows in the Providers table that match those requirements, namely the providers with ProviderNumber 116 and 101.
Kindest Regards, Rod Connect with me on LinkedIn.
July 2, 2012 at 9:39 am
I don't see any subqueries in the code provided, so I'm confused as to why you would get this error message on the simple update provided.
July 2, 2012 at 9:55 am
Is there a trigger on the table? I'd guess that the trigger can't handle the multiple rows being updated, and it's the trigger that's giving you the message. The update syntax you've shown wouldn't generate that error.
July 2, 2012 at 11:43 am
I hadn't thought of triggers. There are 2 triggers on the Providers table. The first one written back in 1999, for INSERT only, is this:
/*
* PREVENT NULL VALUES IN 'ProviderNumber'
*/
IF (SELECT Count(*) FROM inserted WHERE ProviderNumber IS NULL) > 0
BEGIN
RAISERROR 44444 'Field ''ProviderNumber'' cannot contain a null value.'
ROLLBACK TRANSACTION
END
ELSE
/*
* PREVENT NULL VALUES IN 'Description'
*/
IF (SELECT Count(*) FROM inserted WHERE Description IS NULL) > 0
BEGIN
RAISERROR 44444 'Field ''Description'' cannot contain a null value.'
ROLLBACK TRANSACTION
END
The second one, written all the way back in 1998, is this:
ALTER TRIGGER [dbo].[utProviders2] ON [dbo].[Providers]
FOR UPDATE
AS
DECLARE @StaffCodeErr tinyint,
@ContactErr tinyint,
@AlternateErr tinyint,
@Name varchar(40)
/* Check the StaffCode column in the Providers table */
IF (SELECT inserted.StaffCode
FROM inserted
WHERE inserted.StaffCode NOT IN
(SELECT inserted.StaffCode
FROMinserted, deleted
WHEREinserted.StaffCode = deleted.StaffCode)) > 0
BEGIN
SELECT@StaffCodeErr = COUNT(*)
FROMProviders, inserted
WHEREProviders.ProviderNumber = inserted.ProviderNumber AND
Providers.StaffCode NOT IN
(SELECTProviderStaff.StaffCode
FROMProviderStaff
WHEREProviders.ProviderNumber = ProviderStaff.ProviderNumber)
END
/* Check the Contact column in the Providers table */
IF (SELECT inserted.Contact
FROM inserted
WHERE inserted.Contact NOT IN
(SELECT inserted.Contact
FROMinserted, deleted
WHEREinserted.Contact = deleted.Contact)) > 0
BEGIN
SELECT@ContactErr = COUNT(*)
FROMProviders, inserted
WHEREProviders.ProviderNumber = inserted.ProviderNumber AND
Providers.Contact NOT IN
(SELECTProviderStaff.StaffCode
FROMProviderStaff
WHEREProviders.ProviderNumber = ProviderStaff.ProviderNumber)
END
/* Check the AlternateContact column in the Providers table */
IF (SELECTinserted.AlternateContact
FROMinserted
WHEREinserted.AlternateContact NOT IN
(SELECT inserted.AlternateContact
FROMinserted, deleted
WHEREinserted.AlternateContact = deleted.AlternateContact)) > 0
BEGIN
SELECT@AlternateErr = COUNT(*)
FROMProviders, inserted
WHEREProviders.ProviderNumber = inserted.ProviderNumber AND
Providers.AlternateContact NOT IN
(SELECTProviderStaff.StaffCode
FROMProviderStaff
WHEREProviders.ProviderNumber = ProviderStaff.ProviderNumber)
END
/* If any of the 'bad-data' flags have set, then rollback the transaction. */
IF (@StaffCodeErr > 0) OR (@ContactErr > 0) OR (@AlternateErr > 0)
BEGIN
SELECT @Name=inserted.Description
FROM inserted
RAISERROR(50012,16,-1,@Name)
ROLLBACK TRANSACTION
END
Kindest Regards, Rod Connect with me on LinkedIn.
July 2, 2012 at 1:26 pm
I think this construct:
IF (SELECT inserted.StaffCode
FROM inserted
WHERE inserted.StaffCode NOT IN
(SELECT inserted.StaffCode
FROMinserted, deleted
WHEREinserted.StaffCode = deleted.StaffCode)) > 0
is causing the error when more than 1 row is updated. Also, the final select that pulls the description to display if there is an error will only display 1 description. I get the same error with a select I know returns more than 1 row.
July 3, 2012 at 11:30 am
You might try something like below:
DECLARE @StaffCodeErr tinyint,
@ContactErr tinyint,
@AlternateErr tinyint,
@Name varchar(400)
set @Name = ' '
/* Check the StaffCode column in the Providers table */
SELECT@StaffCodeErr = COUNT(*)
FROMProviders, inserted
WHEREProviders.ProviderNumber = inserted.ProviderNumber AND
Providers.StaffCode NOT IN
(SELECTProviderStaff.StaffCode
FROMProviderStaff
WHEREProviders.ProviderNumber = ProviderStaff.ProviderNumber)
END
/* If any of the 'bad-data' flags have set, then rollback the transaction. */
IF (@StaffCodeErr > 0) OR (@ContactErr > 0) OR (@AlternateErr > 0)
BEGIN
select @Name = @Name + ',' + description from inserted
RAISERROR(50012,16,-1,@Name)
ROLLBACK TRANSACTION
END
This will set the @name = to a space to start with so the description concatenation will work ( @name has to be bigger to hold multiple descriptions). Then do the count for each validation and the final check at the end will grab all the descriptions (@name has to be big enough to hold them all).
Just one way of doing it, other may have better suggestions.
July 3, 2012 at 2:58 pm
I think you nailed it, David. I just disabled that update trigger, and I can now update values in the Providers table fine. Man, that's irritating! I've got to go back over that and try to find out what the original coder was trying to do, and see if I can get it done some other way.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply