Pass mysql query as array to mysql In statement

Question

I have a query like

UPDATE myproject.empinfo SET country="XYZ" WHERE emp_id = 2 order by stars desc limit 5 ;
SELECT id FROM myproject.emp where version = 'New'

What it will do is it sets the top 5 employee country to XYZ sorted by their stars.

But in this query I have given the emp_id to 2 ...I can do it for one emp easily but now I want a query that can update all empinfo for employee in emp table.

It should get ids of all emp as in my second query and make them an array like (1,2,3,..)

and pass it like

UPDATE myproject.empinfo SET country="XYZ" in($employeeIdarray) order by stars desc limit 5 ;

that employeeidarray should be the array of ids

How can I do this in one query?

Are you using PHP? It looks like you are with $employeeIdarray. If so, tag your question with the PHP tag, and use implode(",", $employeeIdarray) to comma separate your values. — ʰᵈˑ
– ʰᵈˑ, Commented Nov 28, 2014 at 12:56

ʰᵈˑ · Accepted Answer · 2014-12-01 08:22:13Z

1

You can use GROUP_CONCAT and comma separate them that way.

UPDATE myproject.empinfo 
SET country="XYZ" 
WHERE emp_id IN(select GROUP_CONCAT(id SEPARATOR ',') 
                  from myproject.emp 
                  where version = 'New') 
ORDER BY stars desc 
LIMIT 5;

edited Dec 1, 2014 at 8:22

answered Nov 28, 2014 at 14:32

ʰᵈˑ

11.4k3 gold badges31 silver badges50 bronze badges

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

Collectives™ on Stack Overflow

Pass mysql query as array to mysql In statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related