mysql computed field

Posted on 2004-11-01
Last Modified: 2012-06-27

I'm not sure if i can do this, but is it possible to make a computed field in mysql, like say for instance I want to have a field called fullname which will automaticaly create a users full name from the field firstname and lastname. Also, if this is possible is it possible to have computed fields between tables, like say for instance i have a table for a customers quotes, with a dollar amount, and then i have another table called customerTotals which gives the customers total dollar amount of quotes for the year, is it possible to have this total dollar amount of quotes for the year field calculated even tho they are in different tables?


- Loren
Question by:LTY83
    LVL 3

    Expert Comment

    i dont think this is possible... mysql is just storage, i dont think it has these features

    if im wrong, someone plz correct me... this sounds rather interesting if it does exist
    LVL 14

    Accepted Solution

    for the first question, yes :

    SELECT CONCAT_WS(' ', firstname, lastname) AS fullname
    FROM customers

    for the second question probably yes but i don't have your table structure. it should be something like this :

    SELECT CONCAT_WS(' ', c.firstname, c.lastname) AS fullname, ct.amout, SUM(cq.quotes) AS yearquotes
    FROM customers AS c INNER JOIN customertotal AS ct ON c.customerid = ct.customerid
                                     INNER JOIN customerquotes AS cq ON (c.customerid = cq.customerid AND ct.year = cq.year)
    GROUP BY cq.customerid, cq.year
    LVL 4

    Author Comment

    but can this be done in the table automatically, IE: data is entered thru asp/sql but in the table the full name field generates itself?
    LVL 14

    Expert Comment

    yes, you can update a field with a computed expression :

    UPDATE customer SET fullname = CONCAT_WS(' ', firstname, lastname)
    WHERE fullname IS NULL

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    IT, Stop Being Called Into Every Meeting

    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!

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    This video Micro Tutorial is the first in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 ( But the ability to create custom scanning profiles al…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    875 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