CODEWITHSUNDEEP
mysqlsql
Evan
Evan

Reputation: 2625

Possible to automatically set value in a column depending on other values from same row?

Let's say my row have columns: A, B, and AB

If I were to INSERT value 5 for column A and value 2 for column B, is it possible for MySQL or something to automatically take these values and do something with it, such as multiply and store it into a specific column, such as AB?

My basic goal is to get (using SQL query) the top 5 values in the table based on the multiplication of A and B.

How do I do this?

Upvotes: 1

Views: 1282

Answers (3)

Derek W
Derek W

Reputation: 10026

When inserting into the columns of a and b just insert the multiplication of the two into the third column like this.

INSERT INTO Sample (a, b, ab) VALUES (5, 10, 5*10);

SQL Fiddle of it in action here.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562260

One solution to make a column automatically calculate a column based on other columns is to use a trigger:

mysql> DELIMITER !

mysql> CREATE TRIGGER calc_ab_ins BEFORE INSERT ON mytable 
FOR EACH ROW BEGIN
  SET NEW.ab = NEW.a * NEW.b;
END !

mysql> CREATE TRIGGER calc_ab_upd BEFORE UPDATE ON mytable 
FOR EACH ROW BEGIN
  SET NEW.ab = NEW.a * NEW.b;
END !

mysql> DELIMITER ;

Upvotes: 3

echo_Me
echo_Me

Reputation: 37233

yes , yes , yes , yes ,yes , yes

just do this if you want insert them like that.

    Insert into table (column_ab) values (a * b)

or just when you select do like that

   select a , b , a*b as multip from your_table Order by multip desc LIMIT 5

this you will get multiplied values of a and b and top 5 .

Upvotes: 2

Related Questions