mysql - UPDATEing row based on other rows

I'm looking to update certain rows based on specific criteria based on other rows. Lets say the table looks like this:

COLUMNS:   time              type     genre    doubles     triples
ROW 1:    2010.06.21 12:00    1        1        0            0
ROW 2:    2010.06.21 12:00    1        2        0            0
ROW 3:    2010.06.21 12:00    1        1        0            0
ROW 4:    2010.06.21 12:00    2        3        0            0
ROW 5:    2010.06.22 12:00    2        2        0            0
ROW 6:    2010.06.22 12:00    2        3        0            0
ROW 7:    2010.06.22 12:00    1        1        0            0

I'm looking to update the doubles and triples columns based on the following rules:

1) Only look to update rows where the time=time and type=type between rows (e.g. Rows 1,2,3 and rows 5 & 6).

2) Next count the # of different genre's between those rows and if there are two different genres then change the doubles column to 1, or if there are three then change the triples column to 1. SO for example in the table above rows 1,2,3 would have doubles=1 because between the three rows there are two different genres. Rows 5 and 6 would also have doubles=1 because there is again two d开发者_StackOverflowifferent genres between the rows. Doubles can =1 and triples can =1 but not both.

Now, I could write up some PHP based on these rules pretty easily I think, but I'm wondering if there is a way to do it all in mysql? It seems like I'm always surprised the amount you can do from a SQL statement.

Maybe something like (two different statements for the doubles and triples column):

Doubles - UPDATE myTable SET (doubles=1) WHERE time=time AND type=type ... but then how would you account for rule #2 above (counting number of rows with unique genres).

Is this possible in mysql or is PHP just the right way to go here?

Thanks in advance

---

Sure, you can do it in one query. Using this sample table:

create table duotri (time varchar(100), type int, genre int, doubles int, triples int);
insert duotri values
('2010.06.21 12:00'    ,1        ,1        ,0            ,0),
('2010.06.21 12:00'    ,1        ,2        ,0            ,0),
('2010.06.21 12:00'    ,1        ,1        ,0            ,0),
('2010.06.21 12:00'    ,2        ,3        ,0            ,0),
('2010.06.22 12:00'    ,2        ,2        ,0            ,0),
('2010.06.22 12:00'    ,2        ,3        ,0            ,0),
('2010.06.22 12:00'    ,1        ,1        ,0            ,0);

The update statement should join to the GROUPed form to get the doubles and triples.

update duotri t1
inner join (
    select time, type,
     case when count(distinct genre) = 2 then 1 else 0 end doubles,
     case when count(distinct genre) = 3 then 1 else 0 end triples
    from duotri
    group by time, type) t2
   on t1.time=t2.time and t1.type=t2.type
set t1.doubles=t2.doubles, t1.triples = t2.triples;

---

It may be possible using sub-selects and the CASE expression or by comparing the count to the count for a column, but it will be difficult due to the fact that MySQL doesn't let you select the table that's being updated in the subselect.

You can do it easily in a select (I'm giving the table the name of 'recordings', since you didn't specify it; giving samples in SQL rather than preformatted text is always helpful):

SELECT r0.id, r0.`time`, r0.`type`, r0.`genre`,
     COUNT(DISTINCT r1.genre) = 2 AS doubles,
     COUNT(DISTINCT r1.genre) = 3 AS triples
  FROM recordings AS r0
    JOIN recordings AS r1 ON r0.`time`=r1.`time` AND r0.`type`=r1.`type`
  GROUP BY r0.`id`, r0.`time`, r0.`type`, r0.`genre`

You could then use this as the basis for a stored procedure or a view:

ALTER TABLE recordings DROP COLUMN doubles;
ALTER TABLE recordings DROP COLUMN triples;
ALTER TABLE recordings ADD id INT PRIMARY KEY AUTO_INCREMENT;

CREATE VIEW recording_genre_count AS
SELECT r0.id, r0.`time`, r0.`type`, r0.`genre`,
     CASE COUNT(DISTINCT r1.genre) WHEN 2 THEN 1 ELSE 0 END AS doubles,
     CASE COUNT(DISTINCT r1.genre) WHEN 3 THEN 1 ELSE 0 END AS triples
  FROM recordings AS r0
    JOIN recordings AS r1 ON r0.`time`=r1.`time` AND r0.`type`=r1.`type`
  GROUP BY r0.`id`, r0.`time`, r0.`type`, r0.`genre`;
  --Since the columns of recordings are functionally dependent on recordings.id,
  --you could use just "GROUP BY r0.id", but the above will work on other
  -- DBMSs

If groups change frequently, the view is a better choice. It also ensures data consistency. If you have to run a query to update the doubles and triples columns, the DB will often be in an inconsistent state immediately after rows are inserted (triggers could help with this, but like sub-selects in UPDATEs, triggers have problems selecting from a table that's being updated). Views and queries also more readily allow for additional columns (e.g. quadruples) than a stored procedure.