error query update mysql

Question

I have an query:

update customers_training_malaysia
set
     period_id = (select b.id 
                  from customers_training_malaysia a,training_schedules_malaysia b
                  where a.sch_code=b.sch_code order by a,id)
where
     sch_code = (select b.sch_code 
                 from customers_training_malaysia a,training_schedules_malaysia b
                 where a.sch_code = b.sch_code order by a.id)

I tried to run following query update, but I get only the error

more than one row returned by a subquery used as an expression

What shall I do to correct the sql query?

Maybe try adding a limit 1 to your subquery?

My Other Me
– My Other Me

2011-02-17 08:12:09 +00:00
Commented Feb 17, 2011 at 8:12 — My Other Me
– My Other Me, Commented Feb 17, 2011 at 8:12

Sergey Vedernikov · Accepted Answer · 2011-02-17 08:35:04Z

1

You must get all your subqueries in

set [field_name] = ([subquery])

and check that this queries returning only one record in results. That is reason for the error - multiple results in your subqueries

Try this:

update customers_training_malaysia
set
     period_id = b.id
where
     sch_code = (select b.sch_code 
                 from customers_training_malaysia a,training_schedules_malaysia b
                 where a.sch_code = b.sch_code order by a.id)

edited Feb 17, 2011 at 8:35

answered Feb 17, 2011 at 8:08

Sergey Vedernikov

7,7542 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harish · Accepted Answer · 2011-02-17 08:25:34Z

0

order by a,id)

Is this a typo?

**","** used it should be a.id

And Add from the other answer Below it might be returning more than one result, because of order by condition i assumed that!

answered Feb 17, 2011 at 8:25

Harish

2,3344 gold badges24 silver badges28 bronze badges

Collectives™ on Stack Overflow

error query update mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related