UPDATE statement

Question

Post reply

UPDATE statement

Opus

Default port

Points: 1419
More actions
September 15, 2010 at 7:54 am

#229298

I have a table with multiple years of data but the state_average_percent & district_average_percent data is missing for the current year:
CREATE TABLE [dbo].[crt_concept](
[test_uniq] [int] NULL,
[concept_id] [smallint] NULL,
[test_id] [varchar](12) NULL,
[concept_name] [varchar](100) NULL,
[questions_count] [smallint] NULL,
[objectives_count] [smallint] NULL,
[active] [char](1) NULL,
[state_average_percent] [decimal](6, 3) NULL,
[district_average_percent] [decimal](6, 3) NULL,
[reporting_entity] [char](2) NULL
This table has the current year data including the state_average_percent & district_average_percent data:
CREATE TABLE [dbo].[Ken_Concepts](
[test_uniq] [int] NULL,
[test_id] [varchar](12) NULL,
[test_description] [varchar](30) NULL,
[concept_name] [varchar](100) NULL,
[concept_id] [smallint] NULL,
[state_average_percent] [decimal](6, 3) NULL,
[district_average_percent] [decimal](6, 3) NULL
This is my attempt to update the crt concept table:
UPDATE crt_concept
SET state_average_percent = (SELECT kc.state_average_percent
FROM Ken_Concepts kc, crt_concept cc
WHERE cc.test_uniq = kc.test_uniq
and cc.concept_id = kc.concept_id
and cc.test_id = kc.test_id),
district_average_percent = (SELECT kc.district_average_percent
FROM Ken_Concepts kc, crt_concept cc
WHERE cc.test_uniq = kc.test_uniq
and cc.concept_id = kc.concept_id
and cc.test_id = kc.test_id)
Error: 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.
Am I approaching this correctly? Also, reading through some other posts, there is a document somewhere called "Help Us Help You". Can someone point me to it?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

You're missing a comma after the first subquery:

and test_id = kc.test_id),

district_average_percent = (SELECT district_average_percent

If that doesn't resolve it, then you're probably getting more than 1 result from one of the subqueries, which obviously won't work (SQL wouldn't know which value to set the field to. Try forcing some data using variables and testing your selects to see what data you get back.

Opus Default port Points: 1419 More actions · Answer 2

I tweaked the code to add the comma and crt_concept alias to the select statements.

The combination of test_uniq, concept_id, test_id is unique since the following does not return any rows.

select COUNT(*),

test_uniq,

concept_id

test_id

from Ken_Concepts

group by

test_uniq,

concept_id,

test_id

having count(*) > 1

When I run the select by them selves, I return the expected data.

Derrick Smith SSChampion Points: 10833 More actions · Answer 3

Whoops - you're doing a cartesian join on your subqueries. It should be an inner join on those 3 fields. Haven't had my coffee yet...will update with a new query in 1 min.

try this:

UPDATE crt_concept

SET state_average_percent = (

SELECT kc.state_average_percent

FROM Ken_Concepts kc

INNER JOIN crt_concept cc ON

cc.test_uniq = kc.test_uniq

and cc.concept_id = kc.concept_id

and cc.test_id = kc.test_id

),

district_average_percent = (

SELECT kc.district_average_percent

FROM Ken_Concepts kc

INNER JOIN crt_concept cc ON

cc.test_uniq = kc.test_uniq

and cc.concept_id = kc.concept_id

and cc.test_id = kc.test_id

)

Opus Default port Points: 1419 More actions · Answer 4

I still receive: 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.

When I run the select statements, I return the 169 rows that I expect. I'm confused as to what why it thinks more than 1 value is being returned.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 5

I find it a lot easier to use the Update From statement, here's how I would do it:

UPDATE crt_concept

SET state_average_percent = kc.state_average_percent ,

district_average_percent = kc.district_average_percent

FROM Ken_Concepts kc

INNER JOIN crt_concept cc ON cc.test_uniq = kc.test_uniq

AND cc.concept_id = kc.concept_id

AND cc.test_id = kc.test_id

Test it to see if it works.

Also, it easier this way to see what is returned, just change the Update Set to a "select *", and you get expected results.

Cheers,

J-F

Opus Default port Points: 1419 More actions · Answer 6

That did it! I could have sworn I try this method but I'll have to go back tand check my notes. Thanks to the both of you for your efforts!

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 7

J-F Bergeron

SSCrazy Eights

Points: 8593

September 15, 2010 at 12:07 pm

#1221581

Glad it helped,

Have a nice day,

Cheers,

J-F