March 2, 2005 at 3:44 pm
Question: I am receiving the below error when I added the "update" to the first of my script:
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.
What am I doing wrong?
Script:
update contact2
set udefcon =
(Select
CASE
WHEN key1 IN('APP', 'ADM', 'CNF', 'DNY', 'ENR') THEN 100 --Add 100 points if key1 has a VALUE in the field
ELSE 0
END +
CASE
WHEN uactequiv BETWEEN 1 AND 19 THEN 25 -- add points for uactequiv value
WHEN uactequiv BETWEEN 20 AND 21 THEN 50
WHEN uactequiv BETWEEN 22 AND 23 THEN 75
When uactequiv BETWEEN 24 and 26 THEN 100
When uactequiv BETWEEN 27 and 30 THEN 125
WHEN uactequiv >=31 THEN 150
ELSE 0
END +
CASE
WHEN userdef02 IS NOT NULL THEN 50 -- Add 50 points if userdef02 not null
ELSE 0
END +
CASE
WHEN userdef03 IS NOT NULL THEN 50 -- Add 50 points if userdef03 not null
ELSE 0
END +
CASE
WHEN uconflg = 'Y' THEN 150 --Add 150 points if uconflg has a Y in the field
ELSE 0
END +
CASE
WHEN UVSARRIVDT > ' ' THEN 150 --Add 150 points if UVSARRIDT has a date in the field
ELSE 0
END +
CASE
WHEN uacurank = '1' THEN 50 --Add 50 points if UACURANK has a 1 in the field
ELSE 0
END +
CASE
WHEN uacurank = '2' THEN 30 --Add 30 points if UACURANK has a 2 in the field
ELSE 0
END +
CASE
WHEN uacurank = '3' THEN 20 --Add 20 points if UACURANK has a 3 in the field
ELSE 0
END +
CASE
WHEN uleadcamp = 'Y' THEN 100 --Add 100 points if uleadcamp equals Y
ELSE 0
END +
CASE
when uacadarea1 IN ('BUS', 'BID', 'JOU', 'PSY', 'MED', 'DEN') OR
umajor IN ('ACCT', 'FIN', 'MKTG', 'MGMT', 'BIBL', 'BIBM', 'BIBY', 'BIVM', 'BMCF', 'BMIS', 'BYFM', 'IMC', 'JELM', 'JOUR', 'PSYC') OR
department IN ('PRDE', 'PRME') THEN 50
ELSE 0
END +
CASE
when upsupport = 'Y' THEN 50 --add 50 points if upsupport is marked Y
ELSE 0
END +
CASE
WHEN ubot >' ' THEN 100 --add 100 points if ubot has a VALUE in the field
ELSE 0
END +
CASE
WHEN ugoldlist >' ' THEN 100 --add 100 points if ugoldlist has a VALUE in the field
ELSE 0
END +
CASE
WHEN ufacstaff >' ' THEN 100 --add 100 points if ufacstaff has a VALUE in the field
ELSE 0
END +
CASE
WHEN ualumni = 'Y' THEN 50 --add 50 points if ualumni has a VALUE that equals Y
ELSE 0
END +
CASE
WHEN usibling = 'Y' THEN 100 --add 100 points if usibling has a VALUE that equals Y
ELSE 0
END +
CASE
WHEN uchurchact IN('A', 'B') THEN 50 --add 50 points if uchurchact has an A or B in the field
ELSE 0
END AS TOTALSCORE
FROM
contact2, contact1
WHERE contact1.accountno = contact2.accountno)
March 2, 2005 at 4:26 pm
Let me simplify the problem for you.
update contact2
set udefcon =
( SELECT 1
FROM
contact2, contact1
WHERE contact1.accountno = contact2.accountno)
The above should still fail with the same error. The reason is that the select part will return more than one row. If you run it all by itself you will likely get a lot of results (one for each accountno). The problem is that you are trying to assign all of those values to a single column on a single row albeit for each row within contact2. I believe you need a way to relate the subselect row to the current row being assigned in the update process. I haven't tested the SQL, but basically what you want is the following:
update contact2
set udefcon =
( SELECT 1
FROM
contact2 sub2, contact1 sub1
WHERE sub1.accountno = sub2.accountno
AND sub2.accountno = contact2.accountno)
You may need to add a FROM clause to your UPDATE statement to get things to talk correct, and there may be a more elegant way of extracting your data, but that is the concept you are after. You need the subselect to ONLY pull back the one value associated to the current row that SQL Server is attempting to update at any given moment. If any subselect results in more than one result row then the whole update statement will be rolled back and that error will be given, so be carefull to use the full key in the WHERE clause to ensure that you will get no duplicates.
March 3, 2005 at 8:10 am
I am receiving the below error after adding
update contact2
set udefcon =
(select 1
from
contact2 sub2, contact1 sub1
WHERE sub1.accountno = sub2.accountno
AND sub2.accountno = contact2.accountno
ERROR: Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Can you can call a procedure from an update
like:
update contact2
set udefcon = exec calc_score contact2.accountno @TOTALSCORE = udefcon OUTPUT
NOTE: I have a stored procedure called calc_score. I tried this but it didn't work either.
I also had this: but when I executed it, it worked but I cancelled after 21+ hours.
CREATE PROCEDURE update_score
AS
-- Declare the variables to store the values returned by FETCH.
DECLARE
@acctno varchar(20),
@ttl_score numeric(4)
DECLARE stu_cursor CURSOR FOR
SELECT distinct accountno
FROM contact2
OPEN stu_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM stu_cursor
INTO @acctno
EXECUTE calc_score @acctno, @TOTALSCORE = @ttl_score OUTPUT
UPDATE contact2
SET udefcon = @ttl_score
WHERE accountno = @acctno
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM stu_cursor
INTO @acctno
EXECUTE calc_score @acctno, @TOTALSCORE = @ttl_score OUTPUT
IF @ttl_score > 0
UPDATE contact2
SET udefcon = @ttl_score
WHERE accountno = @acctno
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO
**Of course the calc_score is the script that I submitted yesterday.
Thank you so much for all your help
March 3, 2005 at 9:05 am
First of all, the error suggests that you have more than one column listed in the SELECT portion instead of just the "1" constant value you posted. You may want to double check that. I did create a couple of test tables and run the same update statement successfully. I'm running SQL Server 2000 SP3a in case it may matter, but I doubt it. As for execing a SP in lue of a subquery you can't do that. If you are running SQL Server 2000 you can get the same effect with a User Defined Function. It would be used something like:
update contact2
set udefcon = dbo.calc_score(contact2.accountno)
The UDF would not perform quite as well as the subselect, but would perform tons better than the cursor.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply