CODEWITHSUNDEEP
mysql
user3948234
user3948234

Reputation: 1

How to update data using two tables

I have two tables gains and final_gains.

I'm wondering how I could calculate the sum of two columns and insert it into a different table...I need to be using a WHERE clause which would be inside runescape_name inside the gains table.

Like so

hitpoints_end_exp - hitpoints_starting_exp,
magic_end_exp - magic_starting_exp, 
range_end_exp - range_starting_exp

And insert the result into final_gains.hp_gained, final_gains.magic_gains and final_gains.range_gained

Here are my two tables

gains

+------------------------+-------------+------+-----+---------+-------+
| Field                  | Type        | Null | Key | Default | Extra |
+------------------------+-------------+------+-----+---------+-------+
| runescape_name         | varchar(12) | NO   | PRI | NULL    |       |
| hitpoints_starting_exp | int(50)     | NO   |     | NULL    |       |
| magic_starting_exp     | int(50)     | NO   |     | NULL    |       |
| range_starting_exp     | int(50)     | NO   |     | NULL    |       |
| hitpoints_end_exp      | int(50)     | NO   |     | NULL    |       |
| magic_end_exp          | int(50)     | NO   |     | NULL    |       |
| range_end_exp          | int(50)     | NO   |     | NULL    |       |
+------------------------+-------------+------+-----+---------+-------+

final_gains

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| runescape_name | varchar(12) | NO   | PRI | NULL    |       |
| hp_gained      | int(50)     | NO   |     | NULL    |       |
| magic_gained   | int(50)     | NO   |     | NULL    |       |
| range_gained   | int(50)     | NO   |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Sorry If I'm unclear, trying to explain the best as I can, I hope I'm clear enough

Upvotes: 0

Views: 24

Answers (1)

Trinimon
Trinimon

Reputation: 13957

Use an INSERT, SELECT like this:

INSERT INTO final_gains (runescape_name, hp_gained, magic_gains, range_gained) 
SELECT runescape_name,
       hitpoints_end_exp - hitpoints_starting_exp,
       magic_end_exp - magic_starting_exp, 
       range_end_exp - range_starting_exp
  FROM gains;

In order to avoid duplicate keys:

INSERT INTO final_gains (runescape_name, hp_gained, magic_gains, range_gained) 
SELECT runescape_name,
       hitpoints_end_exp - hitpoints_starting_exp,
       magic_end_exp - magic_starting_exp, 
       range_end_exp - range_starting_exp
  FROM gains
    ON DUPLICATE KEY 
UPDATE hp_gained    = hitpoints_end_exp - hitpoints_starting_exp,
       magic_gains  = magic_end_exp - magic_starting_exp, 
       range_gained = range_end_exp - range_starting_exp;

This is untested code, but should be close.

Note: I removed the first suggestion as it s not applicable to these table definitions. runescape_name is primary key in table final_gains so it has to be inserted/assigned as well.

Upvotes: 1

Related Questions