johnhardy
asked on
MySQL can a View update a table
In a previous thread I learned a little about creating Views.
I created a view which allowed me to bring together two fields
Image1 and imagedirectory and store these in FullPath
select `boats`.`RecNo` AS `RecNo`,`boats`.`Manufactu rer` AS `Manufacturer`,`boats`.`im agedirecto ry` AS `imagedirectory`,`boats`.` image1` AS `image1`,concat(`boats`.`i magedirect ory`,`boat s`.`image1 `) AS `FullPath`,`boats`.`RegDat e` AS `RegDate` from `boats`
I am using Dreamweaver to create an insert page but when I try to insert from the view I get an error
The target table fullpath_working2 of the INSERT is not insertable-into
I thought the view would update the main table but seems my thinking is wrong.
Any help welcomed!
Regards
John
I created a view which allowed me to bring together two fields
Image1 and imagedirectory and store these in FullPath
select `boats`.`RecNo` AS `RecNo`,`boats`.`Manufactu
I am using Dreamweaver to create an insert page but when I try to insert from the view I get an error
The target table fullpath_working2 of the INSERT is not insertable-into
I thought the view would update the main table but seems my thinking is wrong.
Any help welcomed!
Regards
John
ASKER
Thanks Julian
I am trying to use the View to CONCAT fields in the table. Is a View the best way to do this or can it be carried out directly in the table?
I am trying to use the View to CONCAT fields in the table. Is a View the best way to do this or can it be carried out directly in the table?
How do you mean concat fields in the table - do you mean
select concat(firsname, ' ', surname) as fullname from contacts;
Something like that?
You usually use a view to (as the name suggests) create a view of a table or combination of tables.
So for instance if you want to work with a subset of fields in a table or if you want to work with fields spanning two or more tables you could setup a view to present this data which would then simplify the queries required to access the view i.e.
select * from theview;
as opposed to
select c.name, c.surname, a.account_number, d.address from
contacts c left join accounts a on c.contact_id = a.contact_id
left join address d on c.contact_id = a.contact_id
If you want to update the view then you need to follow the guidelines in the link I posted earlier.
select concat(firsname, ' ', surname) as fullname from contacts;
Something like that?
You usually use a view to (as the name suggests) create a view of a table or combination of tables.
So for instance if you want to work with a subset of fields in a table or if you want to work with fields spanning two or more tables you could setup a view to present this data which would then simplify the queries required to access the view i.e.
select * from theview;
as opposed to
select c.name, c.surname, a.account_number, d.address from
contacts c left join accounts a on c.contact_id = a.contact_id
left join address d on c.contact_id = a.contact_id
If you want to update the view then you need to follow the guidelines in the link I posted earlier.
ASKER
Yes I have been thinking about this and it occours to me I do not need to concat fields in the table at all. Just in a View as and when needed only.
Do you agree?
Do you agree?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Julian
That gives me more to get going and have a play around.
Thanks
For the help.
Regards
John
That gives me more to get going and have a play around.
Thanks
For the help.
Regards
John
Read here for more info and post back if you require further assistence
http://dev.mysql.com/doc/refman/5.0/en/view-updatability.html