asked on

MYSQL join in view

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`)))

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

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.

HainKurt

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...

cyberkiwi

@HainKurt

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