MySQL can a View update a table

Posted on 2012-08-16
Last Modified: 2012-08-17
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!

Question by:johnhardy
    LVL 49

    Expert Comment

    by:Julian Hansen
    A view is updatable under certain conditions.

    Read here for more info and post back if you require further assistence

    Author Comment

    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?
    LVL 49

    Expert Comment

    by:Julian Hansen
    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.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.

    Author Comment

    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?
    LVL 49

    Accepted Solution

    I can't say without knowing more about your specific situation

    Consider this

    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


    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.

    Author Closing Comment

    Thanks Julian
    That gives me more to get going and have a play around.

    For the help.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Read about achieving the basic levels of HRIS security in the workplace.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now