Solved

SQL Script to add calculations to fields

Posted on 2014-04-11
4
313 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
  • 2
  • 2
4 Comments
 
LVL 26

Accepted Solution

by:
wilcoxon earned 350 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 26

Assisted Solution

by:skullnobrains
skullnobrains earned 150 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 26

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Here I am using Python IDLE(GUI) to write a simple program and save it, so that we can just execute it in future. Because when we write any program and exit from Python then program that we have written will be lost. So for not losing our program we…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

911 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

22 Experts available now in Live!

Get 1:1 Help Now