Hi,
I am having trouble with an UPDATE query. I have three tables as defined
below. My database holds data for bookings. Each booking relates to a
project. Each project has many tasks, one of which is a booking. I have now
added the column Task_ID to the bookings table. How can I update all the
bookings so that the Task_ID is the same as the Task_ID in the Tasks table
for that project and the task is a booking?
Thanks for your help
mysql> DESCRIBE Bookings;
+-------------------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-------------------------+-------------+------+-----+---------------------+----------------+
| Booking_ID | int(11) | | PRI | NULL |
auto_increment |
| Booking_Type | varchar(15) | | | Unavailability |
|
| User_ID | int(11) | | | 0 |
|
| Project_ID | int(11) | YES | | NULL |
|
| Rep_ID | int(11) | YES | | NULL |
|
| PCT_ID | int(11) | YES | | NULL |
|
| Practice_ID | int(11) | YES | | NULL |
|
| Booking_Creator_ID | int(11) | YES | | NULL |
|
| Booking_Creation_Date | datetime | YES | | NULL |
|
| Booking_Start_Date | datetime | | | 0000-00-00 00:00:00 |
|
| Booking_End_Date | datetime | | | 0000-00-00 00:00:00 |
|
| Booking_Completion_Date | date | YES | | NULL |
|
| Booking_Mileage | int(5) | YES | | NULL |
|
| Booking_Status | varchar(15) | | | Other |
|
| Unavailability_ID | int(2) | YES | | NULL |
|
| Task_ID | int(11) | YES | | NULL |
|
| Work_Type_ID | int(2) | YES | | NULL |
|
| Additional_Notes | text | YES | | NULL |
|
| Form_Recieved | char(3) | | | |
|
| Section_C | char(3) | | | |
|
| Date_Difference | varchar(20) | | | n/a |
|
+-------------------------+-------------+------+-----+---------------------+----------------+
21 rows in set (0.01 sec)
mysql> DESCRIBE Projects;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| Project_ID | int(11) | | PRI | NULL | auto_increment |
| Project_Name | varchar(100) | | | | |
| Client_ID | int(11) | | | 0 | |
| Rep_Viewable | char(3) | | | Yes | |
| User_ID | int(11) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> DESCRIBE Tasks;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| Task_ID | int(11) | | PRI | NULL | auto_increment |
| Task_Name | varchar(40) | | | | |
| Project_ID | int(11) | | | 0 | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql>
