Re: Update Query with special conditions.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I am curious about doing something simular to this... does anyone have an idea


On Wed, 24 Nov 2004 00:43:32 -0500, list 123. <list> wrote:
> Using mySQL 4.0, I would like to know how I can code a query that will change the value of Participants.Active from Y to N is for three or more CONSECUTIVE sessions they have Attendance.Present = 'No'?
> 
> The Attendance Table has Attendance.Session which coresponds to Sessions.SessionID and Attendance.Participant coresponds to Participants.Part_ID;
> 
> To assist, I have shown you the data of the Sessions and the descriptions of Attendance, Participants, Attendance
> 
> Thanks
> G
> 
> mysql> describe Participants;
> +-----------+-----------------------+------+-----+---------+----------------+
> | Field     | Type                  | Null | Key | Default | Extra          |
> +-----------+-----------------------+------+-----+---------+----------------+
> | Part_ID   | smallint(10) unsigned |      | PRI | NULL    | auto_increment |
> | LastName  | varchar(30)           |      | PRI |         |                |
> | FirstName | varchar(30)           |      | PRI |         |                |
> | DOB       | date                  | YES  |     | NULL    |                |
> | Sex       | enum('M','F')         |      |     | M       |                |
> | Phone1    | varchar(12)           |      | MUL |         |                |
> | Phone2    | varchar(12)           | YES  |     | NULL    |                |
> | Notes     | text                  |      |     |         |                |
> | Facesheet | enum('Have','Need')   |      |     | Need    |                |
> | Active    | set('Y','N')          |      |     | Y       |                |
> +-----------+-----------------------+------+-----+---------+----------------+
> 10 rows in set (0.00 sec)
> 
> mysql> describe Attendance;
> +-------------+------------------+------+-----+---------+----------------+
> | Field       | Type             | Null | Key | Default | Extra          |
> +-------------+------------------+------+-----+---------+----------------+
> | AttID       | int(4)           |      | PRI | NULL    | auto_increment |
> | Session     | int(2)           |      | MUL | 0       |                |
> | Participant | int(2)           |      |     | 0       |                |
> | Present     | enum('Yes','No') |      |     | Yes     |                |
> +-------------+------------------+------+-----+---------+----------------+
> 4 rows in set (0.00 sec)
> 
> mysql> describe Sessions;
> +-------------+-----------------+------+-----+------------+----------------+
> | Field       | Type            | Null | Key | Default    | Extra          |
> +-------------+-----------------+------+-----+------------+----------------+
> | SessionID   | int(2) unsigned |      | PRI | NULL       | auto_increment |
> | SessionDate | date            |      | PRI | 0000-00-00 |                |
> +-------------+-----------------+------+-----+------------+----------------+
> 2 rows in set (0.03 sec)
> 
> mysql> select * from Sessions;
> +-----------+-------------+
> | SessionID | SessionDate |
> +-----------+-------------+
> |         1 | 2004-10-30  |
> |         2 | 2004-11-06  |
> |         3 | 2004-11-13  |
> |         4 | 2004-11-20  |
> |         5 | 2004-12-04  |
> |         6 | 2004-12-11  |
> |         7 | 2005-01-08  |
> |         8 | 0000-00-00  |
> |         9 | 2005-01-29  |
> |        10 | 2005-02-05  |
> |        11 | 2005-02-12  |
> |        12 | 2005-02-26  |
> |        13 | 2005-03-05  |
> |        14 | 2005-03-12  |
> |        15 | 2005-03-19  |
> |        16 | 2005-04-02  |
> |        17 | 2005-04-09  |
> |        18 | 2005-04-16  |
> |        19 | 2005-04-23  |
> |        20 | 2005-05-07  |
> |        21 | 2005-05-14  |
> |        22 | 2005-05-21  |
> +-----------+-------------+
> 22 rows in set (0.05 sec)
> 
> +-----------------+
> | Tables_in_AHRC  |
> +-----------------+
> | Attendance      |
> | Participants    |
> | ProgressNotes   |
> | Sessions        |
> | Staff           |
> | StaffAttendance |
> +-----------------+
> 6 rows in set (0.00 sec)
> 
> ________________________________________________________________
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=garyhotko@xxxxxxxxx
> 
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux