Multicolumn IN clause in Mysql

Question

I have an array of arrays like this -

[
  ['mark', 29],
  ['jolly', 28],
  ['john', 19],
  ['jim', 21],
 .
 .
 .     
]

I want to use this in where in clause so that column updates only when both columns name and age matches the subarray, tries to it like this:-

createQueryBuilder()
     ->update('table', 't')
     ->set('t.eligible', 1)
     ->where('t.name', 't.age' IN(:nameAgeArray))
     ->setParameter('nameAgeArray', $nameAgeArray)
     ->getQuery()
     ->execute();

But this way doesnt work. Is there any way to achieve this?

EDIT- Database structure -

name      age     eligible
mark       29      null
john       21      null
Max        20      null

http://stackoverflow.com/questions/31435177/how-to-make-multiple-where-in-column-query-in-doctrine-query-builder — Paul Spiegel, Oct 30 '16 at 12:10

score 0 · Answer 1 · edited May 23 '17 at 12:00

As mentioned in this answer (already mentioned in @PaulSpiegel his comment) you cannot use multiple fields/columns in such clause.
As you can read in this answer here this limitation also applies to orderBy clauses.

To use multiple conditions you will have to split into two arrays, one for name and one for age and then you can do like this:

createQueryBuilder()
    ->update('table', 't')
    ->set('t.eligible', 1)
    ->where('t.name', IN(:nameArray))
    ->andWhere('t.age', IN(:ageArray))
    ->setParameter('nameArray', $nameArray)
    ->setParameter('ageArray', $ageArray)
    ->getQuery()
    ->execute();

Multicolumn IN clause in Mysql

1 Answers1