Subquery returns more than 1 row - help

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

Re: Subquery returns more than 1 row - help

phillip.s.powell@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?

Re: Subquery returns more than 1 row - help

noone wrote:
> phillip.s.powell@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

Re: Subquery returns more than 1 row - help

wrote in message
news:1141178223.807599.104830@i40g2000cwc.googlegroups.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.

Re: Subquery returns more than 1 row - help

Bill Karwin wrote:
> wrote in message
> news:1141178223.807599.104830@i40g2000cwc.googlegroups.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.

Re: Subquery returns more than 1 row - help

wrote in message
news:1141226499.787655.160540@i40g2000cwc.googlegroups.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.

Re: Subquery returns more than 1 row - help

Bill Karwin wrote:
> wrote in message
> news:1141226499.787655.160540@i40g2000cwc.googlegroups.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.