What is wrong with this simple UPDATE query?
-
OK, I am really stumped on this one. I am trying to update some bit columns in a table. I first test my filtering by making a SELECT statement and then turn the SELECT statement into an UPDATE state. I'll give you enough information in order to help illustrate what is going on - specifically I will only give you the columns involved in the query, and nothing else.
Here is the table definition, which includes the primary key and the other columns involved:
CREATE TABLE [dbo].[Providers](
[ProviderNumber] [smallint] NOT NULL,
[Facility] [smallint] NOT NULL,
[AcceptingReferrals] [bit] NULL CONSTRAINT [DF_Providers_AcceptingReferrals] DEFAULT ((1)),
CONSTRAINT [aaaaaProviders_PK] PRIMARY KEY NONCLUSTERED
(
[ProviderNumber] ASC,
[Facility] ASC
)
Now, here is my SELECT statement, which works just fine:
SELECT
Providers
WHERE ProviderNumber = 105
This works fine, as I said, and returns the only 2 rows in the table that match. Now, here is the UPDATE statement:
UPDATE
Providers
SET AcceptingReferrals = 1
WHERE ProviderNumber = 105
This fails with the following error:
"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."
OK, I give up - where is the subquery? I don't see it. When did the number 1 suddenly become a subquery?
Kindest Regards, Rod Connect with me on LinkedIn.
-
Is there a trigger on that table?
Or a DDL trigger?
-
Ninja's,
I had not thought of a trigger. Good idea! I found triggers, one involved with updating the table. It is a long trigger, written back in 1998 by a developer no longer with us, who wanted to use the trigger to do referential integrity checking against another table and columns which I didn't list in the table definition that I gave.
I'll have to investigate it, because I am sure that is the issue.
Thank you!!!
Kindest Regards, Rod Connect with me on LinkedIn.
-
I wouldn't expect it to be anything else. You can't be that blind can ya
. -
Rod,
It is possible that you have more than one ProviderNumber = 105?
Since [aaaaaProviders_PK] is your primary key, can you try the update using that in your Where?
-
Doesn't matter at all... even if he had 10 duplicates per key the update should still work :
DECLARE @a Table (PK INT NOT NULL, Col INT NOT NULL)
INSERT INTO @a (PK, Col)
SELECT 1, 1
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 2, 3
UPDATE @A
SET Col = 2
WHERE PK = 1
--2 row(s) affected
--No error thrown
SELECT * FROM @a
-
Yes, I have 2 records with ProviderNumber = 105; but that is because ProviderNumber and Facility together comprise the primary key.
Like Ninja said, my problem was an update trigger which references another table for referential integrity, against a column which I didn't list in the table definition I gave earlier. The trigger was written by a guy back in 1998, who thought he had to use a trigger to enforce referential integrity, rather than through foreign keys and check constraints. Perhaps that wasn't available in SQL Server back in 1998? I don't know.
Kindest Regards, Rod Connect with me on LinkedIn.
-
It doesn't seem to be a new feature of sql server 2000. I'd assume it was available back in 7.0. Most likely in 6.5 too but I'm just guessing here.
-
I used to work with a programmer who was blind. This was at a government agency. His assignment: Programming the Optical Character Recognition unit.
Go figure.
Government justice having a blind programmer work on main frame OCR.ATBCharles Kincaid
-
Looks to me like he was the man for the job
.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply