0

my php/mysql skills are poor. i have this table:

      id|ball1|ball2|ball3|ball4|prize|
      |1|    2|    5|   10|    4|    0|
      |2|    5|    9|    4|    1|    0|
      |3|    3|    6|    4|    9|    0|
      |4|    4|    8|    2|    3|    0|
      |5|    8|    9|    4|   10|    0|
      |6|    2|    4|    1|   10|    0|

With this query:

$g1=GetRow("SELECT id
     , GROUP_CONCAT(ball ORDER BY ball) balls
FROM 
 ( SELECT id,ball1 ball FROM lottery
   UNION
   SELECT id,ball2 ball FROM lottery
   UNION
   SELECT id,ball3 ball FROM lottery
   UNION
   SELECT id,ball4 ball FROM lottery
 ) x
 WHERE ball IN (2,5,10,4)
 GROUP 
 BY id 
 HAVING COUNT(*) >= 3");

I got this output:

1 - 2,4,5,10

6 - 2,4,10

I want to update the collun "prize" to 1, where count>=3 i tried this:

$sql=sqlQuery("UPDATE lottery set prize=1 where g1=1");

did not work. Tried this too:

   GetRow("UPDATE lottery set premio=1 where ball ORDER BY ball) balls
  FROM 
     ( SELECT id,ball1 ball FROM lottery
   UNION
   SELECT id,ball2 ball FROM lottery
   UNION
   SELECT id,ball3 ball FROM lottery
   UNION
   SELECT id,ball4 ball FROM lottery
 ) x
WHERE ball IN (2,5,10,4)
GROUP 
BY id 
HAVING COUNT(*) >= 3");

Did not work too. Thx.

Thomas Tuur
  • 53
  • 3
  • There's an example of this kind of query here : http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query – nicolas Oct 01 '14 at 23:27
  • I didn´t get it @nicolas. – Thomas Tuur Oct 02 '14 at 00:30
  • Can you add create table statements for lottery and balls ? that would be much easier to help. – nicolas Oct 02 '14 at 00:43
  • `id` int(7) NOT NULL, `ball1` varchar(3) NOT NULL, `ball2` varchar(3) NOT NULL, `ball3` varchar(3) NOT NULL, `ball4` varchar(3) NOT NULL, `premio` int(1) NOT NULL – Thomas Tuur Oct 02 '14 at 01:06
  • Can you tell the exact update you wish to write in english ? Something like "set price to 0 for every lottery that has at least 3 ball..." – nicolas Oct 02 '14 at 01:15
  • Sorry I don't understand precisely what you attempt to do. Can you explain with words ? – nicolas Oct 02 '14 at 01:17
  • I want to update all the ids (with premio=1) where has at least 3 balls from the drawn (2,5,10,4). – Thomas Tuur Oct 02 '14 at 01:19

1 Answers1

1

Can you try that ?

UPDATE lottery set prize = '1' where premio = '1' and
IF(ball1 IN(2,5,10,4),1,0)+
IF(ball2 IN(2,5,10,4),1,0)+
IF(ball3 IN(2,5,10,4),1,0)+
IF(ball4 IN(2,5,10,4),1,0) >= 3

Far less complicated than using update and select together.

nicolas
  • 712
  • 4
  • 15