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

x
?
Solved

SQL Script to add calculations to fields

Posted on 2014-04-11
4
Medium Priority
?
337 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 27

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 27

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
A set of related code is known to be a Module, it helps us to organize our code logically which is much easier for us to understand and use it. Module is an object with arbitrarily named attributes which can be used in binding and referencing. …
The viewer will learn how to dynamically set the form action using jQuery.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
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