• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1073
  • Last Modified:

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`.`Manufacturer` AS `Manufacturer`,`boats`.`imagedirectory` AS `imagedirectory`,`boats`.`image1` AS `image1`,concat(`boats`.`imagedirectory`,`boats`.`image1`) AS `FullPath`,`boats`.`RegDate` 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
0
johnhardy
Asked:
johnhardy
  • 3
  • 3
1 Solution
 
Julian HansenCommented:
A view is updatable under certain conditions.

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
0
 
johnhardyAuthor Commented:
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?
0
 
Julian HansenCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
johnhardyAuthor Commented:
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?
0
 
Julian HansenCommented:
I can't say without knowing more about your specific situation

Consider this
filetable
    file
    path

I might want to have a view of the full path to the file which would be

select concat(path, '/', file) from filetable;

This would be an acceptable view

On the other hand

Contact
  name
  surname
  age

select concat(name, ' ', surname, ' is ', age , ' years old') from Contact;

Might not be useful if I want to be able to access the individual fields from the result.

Depends on your circumstances.
0
 
johnhardyAuthor Commented:
Thanks Julian
That gives me more to get going and have a play around.

Thanks
For the help.

Regards

John
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now