Solved

SQL Script to add calculations to fields

Posted on 2014-04-11
4
310 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Dictionaries contain key:value pairs. Which means a collection of tuples with an attribute name and an assigned value to it. The semicolon present in between each key and values and attribute with values are delimited with a comma.  In python we can…
Article by: Swadhin
Introduction of Lists in Python: There are six built-in types of sequences. Lists and tuples are the most common one. In this article we will see how to use Lists in python and how we can utilize it while doing our own program. In general we can al…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
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…

708 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

16 Experts available now in Live!

Get 1:1 Help Now