Subquery returns more than 1 row - help

[phillip.s.powell]

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

[noone]

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?

[phillip.s.powell]

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

[Bill Karwin]

<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.

[phillip.s.powell]

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.

[Bill Karwin]

<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.

[phillip.s.powell]

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.