Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

mysql computed field

Posted on 2004-11-01
4
Medium Priority
?
910 Views
Last Modified: 2012-06-27
Hello,

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?

Thanks!

- Loren
0
Comment
Question by:LTY83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Expert Comment

by:alskdj80
ID: 12465613
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
0
 
LVL 14

Accepted Solution

by:
psadac earned 2000 total points
ID: 12466148
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
 
0
 
LVL 4

Author Comment

by:LTY83
ID: 12466180
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?
0
 
LVL 14

Expert Comment

by:psadac
ID: 12466270
yes, you can update a field with a computed expression :

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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