MYSQL join in view

J C
J C used Ask the Experts™
on
I have two tables. Table1=Tasks and Table2=Time

I am trying to create a grid/form that will allow my users to enter their task and time all at once. Can I use a view to insert/delete records in both tables? When I try to enter a record into the view, the auto-incremented fields do not function as they normally do(which may be normal behavior) and I receive an error when it attempts to insert it. 1471 - the target table view_task_time of the INSERT is not insertable-into

Is there anything I add or tweak in the definition that will allow me to insert? Also, the TASKID is the value I use to link to the Time table from TASKS. I need to make sure the TASKID that should be auto incremented for the TASK.TASKID gets written to the TIME.TASKID field as well.

Here is the definition:

SELECT
tasks.TASKID AS TASKID,
tasks.DESCRIPTION AS DESCRIPTION,
tasks.DATEREQUESTED AS DATEREQUESTED,
tasks.DUEDATE AS DUEDATE,
tasks.REQUESTORFIRSTNAME AS REQUESTORFIRSTNAME,
tasks.REQUESTORLASTNAME AS REQUESTORLASTNAME,
tasks.TECHNAME AS TECHNAME,
tasks.TECHID AS TECHID,
tasks.REQUESTORID AS REQUESTORID,
tasks.CLIENTID AS CLIENTID,
tasks.TASKTYPE AS TASKTYPE,
tasks.TASKTYPEID AS TASKTYPEID,
tasks.NOTES AS NOTES,
tasks.PRIORITY AS PRIORITY,
tasks.PRIORITYID AS PRIORITYID,
tasks.LOCATIONNAME AS LOCATIONNAME,
tasks.LOCATIONID AS LOCATIONID,
tasks.COMPLETIONDATE AS COMPLETIONDATE,
tasks.CLIENTNAME AS CLIENTNAME,
tasks.COMPLETE AS COMPLETE,
tasks.BLUEFOLDERTASKID AS BLUEFOLDERTASKID,
tasks.BLUEFODLERTECHID AS BLUEFODLERTECHID,
tasks.PendingTasks AS PendingTasks,
tasks.ClosedTasks AS ClosedTasks,
tasks.OverdueTasks AS OverdueTasks,
tasks.UnassignedTasks AS UnassignedTasks,
tasks.Detail AS Detail,
tasks.Critical AS Critical,
tasks.AssignToMe AS AssignToMe,
tasks.techuserid AS techuserid,
time.TASKID AS `time.taskid`,
time.TIMEID AS TIMEID,
time.DATE AS DATE,
time.STARTTIME AS STARTTIME,
time.ENDTIME AS ENDTIME,
time.HOURS AS HOURS,
time.MINUTES AS MINUTES,
time.TECHID AS `time.techid`,
time.TECHNAME AS `time.techname`,
time.TASKDESCRIPTION AS TASKDESCRIPTION,
time.NOTES AS `time.notes`,
time.CLIENTID AS `time.clientid`,
time.CLIENTNAME AS `time.clientname`,
time.BILLABLE AS BILLABLE,
time.TASKIDTIME AS TASKIDTIME
from (`tasks` left join `time` on((`time`.`TASKID` = `tasks`.`TASKID`)))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
You have a JOIN in the view, therefore it is not updatable.

http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the MERGE algorithm. For this to work, the view must use an inner join (not an outer join or a UNION). Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view. Views that use UNION ALL are not permitted even though they might be theoretically updatable, because the implementation uses temporary tables to process them.

For a multiple-table updatable view, INSERT can work if it inserts into a single table. DELETE is not supported.
HainKurtSr. System Analyst

Commented:
or create a view for your query
and use instead of trigger

http://msdn.microsoft.com/en-us/magazine/cc164032.aspx#S1

so when you insert into this view, your trigger will run and you can do whatever you want!

insert into one table, or both or some other tables too...
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
@HainKurt

One can only hope for MySQL to implement that MSSQL feature. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial