By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,638 Members | 2,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,638 IT Pros & Developers. It's quick & easy.

Subquery returns more than 1 row - help

P: n/a
update student s set school_year_id = (select distinct s.id from
school_year s, interns i where lower(s.school_year_name) =
lower(i.enrollment_year)and s.unique_key = i.unique_key group by s.id);
ERROR 1242 (21000): Subquery returns more than 1 row

I am trying to replace a column in interns.student.school_year_id to
read an ID from the school_year table, where school_year_name will map
to interns.interns.enrollment_year

I can't for the life of me figure this one out, please help!

Thanx
Phil

Mar 1 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
ph**************@gmail.com wrote:
update student s set school_year_id = (select distinct s.id from
school_year s, interns i where lower(s.school_year_name) =
lower(i.enrollment_year)and s.unique_key = i.unique_key group by s.id);
ERROR 1242 (21000): Subquery returns more than 1 row

I am trying to replace a column in interns.student.school_year_id to
read an ID from the school_year table, where school_year_name will map
to interns.interns.enrollment_year

I can't for the life of me figure this one out, please help!

Thanx
Phil

can you post the definition of the tables in question?
Mar 1 '06 #2

P: n/a

noone wrote:
ph**************@gmail.com wrote:
update student s set school_year_id = (select distinct s.id from
school_year s, interns i where lower(s.school_year_name) =
lower(i.enrollment_year)and s.unique_key = i.unique_key group by s.id);
ERROR 1242 (21000): Subquery returns more than 1 row

I am trying to replace a column in interns.student.school_year_id to
read an ID from the school_year table, where school_year_name will map
to interns.interns.enrollment_year

I can't for the life of me figure this one out, please help!

Thanx
Phil

can you post the definition of the tables in question?


table students:

id int not null auto_increment, primary key (id),
school_year_id int not null,
unique_key varchar(16) not null

table interns:

id int not null auto_increment, primary key (id),
enrollment_year varchar(80),
unique_key varchar(16) not null

table school_year:

id int not null auto_increment, primary key (id),
school_year_name varchar(50) not null

That's unfortunately all I'm allowed to give you, cannot give full
table definitions, we're not allowed here.

Phil

Mar 1 '06 #3

P: n/a
<ph**************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
update student s set school_year_id = (select distinct s.id from
school_year s, interns i where lower(s.school_year_name) =
lower(i.enrollment_year)and s.unique_key = i.unique_key group by s.id);
ERROR 1242 (21000): Subquery returns more than 1 row


Use IN instead of = when you need to match multiple values returned from the
subquery.

For example:

update student set school_year_id IN (select distinct s.id from
school_year s, interns i where lower(s.school_year_name) =
lower(i.enrollment_year)and s.unique_key = i.unique_key group by s.id);

Also, using the same table alias "s" in both the update and the subquery is
confusing. You don't need the table alias in the update, so I removed it in
the example above.

Regards,
Bill K.
Mar 1 '06 #4

P: n/a

Bill Karwin wrote:
<ph**************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
update student s set school_year_id = (select distinct s.id from
school_year s, interns i where lower(s.school_year_name) =
lower(i.enrollment_year)and s.unique_key = i.unique_key group by s.id);
ERROR 1242 (21000): Subquery returns more than 1 row
Use IN instead of = when you need to match multiple values returned from the
subquery.


I'm sorry but apparently I can't do that in MySQL 4.1.12:

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'IN (
SELECT DISTINCT q.id FROM interns.student_school_enrollment_status q,
inte' at line 1

USING
update student s
set school_year_id IN (
SELECT DISTINCT q.id FROM interns.student_school_enrollment_status q,
interns.interns i
WHERE lower(q.student_school_enrollment_status_name) =
lower(i.enrollment_status)
AND s.unique_key = i.unique_key
)

Phil

For example:

update student set school_year_id IN (select distinct s.id from
school_year s, interns i where lower(s.school_year_name) =
lower(i.enrollment_year)and s.unique_key = i.unique_key group by s.id);

Also, using the same table alias "s" in both the update and the subquery is
confusing. You don't need the table alias in the update, so I removed it in
the example above.

Regards,
Bill K.


Mar 1 '06 #5

P: n/a
<ph**************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
Use IN instead of = when you need to match multiple values returned from
the
subquery.


You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'IN (
SELECT DISTINCT q.id FROM interns.student_school_enrollment_status q,
inte' at line 1


D'ohh! I'm sorry, my mistake. I wasn't looking closely, and didn't notice
this was in the context of an update assignment, not an equals comparison.

I can't tell what you're trying to do in the update. I suspect your logic
has become muddled. Are you trying to do a multi-table update?

Regards,
Bill K.
Mar 1 '06 #6

P: n/a

Bill Karwin wrote:
<ph**************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
Use IN instead of = when you need to match multiple values returned from
the
subquery.
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'IN (
SELECT DISTINCT q.id FROM interns.student_school_enrollment_status q,
inte' at line 1


D'ohh! I'm sorry, my mistake. I wasn't looking closely, and didn't notice
this was in the context of an update assignment, not an equals comparison.

I can't tell what you're trying to do in the update. I suspect your logic
has become muddled. Are you trying to do a multi-table update?


Yes, and someone else on mysqlfreaks.com came up with it, it was so
easy!

update student s, interns i, school_year y set s.school_year_id = y.id
where s.unique_key = i.unique_key and lower(i.enrollment_status) =
lower(y.school_year_name)

Phil

Regards,
Bill K.


Mar 1 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.