?
Solved

SQL Script to add calculations to fields

Posted on 2014-04-11
4
Medium Priority
?
331 Views
Last Modified: 2014-05-22
I have a MySQL database with 10 fields, for example.  I have several, so I am looking for a general idea:

Field1, Field2, Field3 .... Field10 will be the Field names.

I will have Field11 and Field12 as Integer fields that are sometimes empty.  

What I need is to take Field1/Field2 to be the value to populate in the empty Field11 column.
I would then need to take Field6+Field7 and populate the empty Field12 values with those calculations.

I would like to run a script every now and then which does this calc update.
0
Comment
Question by:weklica
[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
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
wilcoxon earned 1400 total points
ID: 39995625
Doesn't MySQL support triggers (I don't know MySQL)?  This would be a perfect use of a trigger.

What is the primary key on this table?  What is the table name?

If that won't work then a script should be pretty trivial.  In perl, it would just be something like:
use strict;
use warnings;
use DBI;
 # replace each var with an appropriate value where they are undef below
my $dbh = DBI->connect("DBI:mysql:database=$db;host=$host;port=$port", $user, $pass);
my $sth = $dbh->prepare(("select Field1, Field2, Field3, Field6, Field7, Field11, Field12 from $table where Field11 is null or Field12 is null");
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
    my $f11 = defined($row->{Field11}) ? 'Field11' : int($row->{Field1}/$row->{Field2});
    my $f12 = defined($row->{Field12}) ? 'Field12' : $row->{Field6}+$row->{Field7};
    $dbh->do("update $table set Field11 = $f11 Field12 = $f12 where $key_column = $key_value");
    } 
}
$sth->finish;

Open in new window

0
 
LVL 27

Assisted Solution

by:skullnobrains
skullnobrains earned 600 total points
ID: 39997471
take Field1/Field2 to be the value to populate in the empty Field11

pretty straightforwards

update table set Field11=Field1/Field2

and likewise for the other demand

---

note that recent versions of mariadb support calculated fields (which are called virtual columns) so you can do this once and for all when you create the table

if not, you can always use a view

create view VIEWNAME select *,Field1/Field2 as field11 from table ORIGINALTABLE

and perform your queries on the view
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39997493
Skullnobrains solution will work if Field11 and Field12 should always be the calculated value.  I took your statement "I will have Field11 and Field12 as Integer fields that are sometimes empty" to mean that Field11 and/or Field12 will sometimes have values populated that are not necessarily Field1/Field2 or Field6+Field7 (and wrote my solution accordingly).
0
 
LVL 27

Expert Comment

by:skullnobrains
ID: 39998315
i assumed that when the fields were populated, they were with that same value.

if not, you can just append "where field11 is null" to the update statement

likewise with a view
... ifnull(field11,field1/field2) as field11

virtual columns don't apply in that case

@wil : yes, mysql/maria does support triggers. both your script and triggers should be able to achieve that same goal
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction On September 29, 2012, the Python 3.3.0 was released; nothing extremely unexpected,  yet another, better version of Python. But, if you work in Microsoft Windows, you should notice that the Python Launcher for Windows was introduced wi…
Flask is a microframework for Python based on Werkzeug and Jinja 2. This requires you to have a good understanding of Python 2.7. Lets install Flask! To install Flask you can use a python repository for libraries tool called pip. Download this f…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

770 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