[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

SQL Script to add calculations to fields

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
weklica
Asked:
weklica
  • 2
  • 2
2 Solutions
 
wilcoxonCommented:
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
 
skullnobrainsCommented:
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
 
wilcoxonCommented:
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
 
skullnobrainsCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now