CODEWITHSUNDEEP
mysqlsqldatabaseselect
Michael Corleone
Michael Corleone

Reputation: 105

Do some calulations of the values in two table and store it in third table mysql

table1

| email           | result  |
----------------------------
| [email protected]   |0.12      |
| [email protected]   |0.23      |
| rthgmail.com    | 0.45     |
| [email protected]   |0.56      |
| [email protected]   | 0.78     |
| [email protected]   | 0.35     |

table2

 | resource        |
 -------------------
 | 0.5             |

table3

| temp            |
-------------------
|NULL             |

Now I want addition of result and resource from table1 and table2 respectively for email [email protected] and save the result in table3. I want to update the table3 every time.

I tried the following but its not working:

UPDATE table3 SET temp = table1.result + table2.resource WHERE email = '[email protected]'

How can I do it guys?

Upvotes: 1

Views: 49

Answers (3)

sagi
sagi

Reputation: 40491

You can do it with a sub query:

UPDATE Table3 t
SET t.temp = (SELECT s.result+p.resource
              FROM table1 s INNER JOIN table2 p
              ON(s.email = '[email protected]'))

If your Table3 doesn't have data yet:

INSERT INTO Table3 
(SELECT s.result+p.resource
 FROM table1 s INNER JOIN table2 p
  ON(s.email = '[email protected]'))

Upvotes: 1

TSungur
TSungur

Reputation: 406

Do you have only one row of data in Table2 and Table3? In that case you could do:

UPDATE Table3 SET temp=SELECT SUM(result)+(SELECT MAX(resource) from table2) from table1 WHERE email = '[email protected]'

Any aggregate function (min, avg etc) other than MAX will do the job (if you really have only one row in table2). And and also identity columns would be better for table2 and table3.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270533

Seems like a curious request, but you want an update with join:

UPDATE table3 t3 CROSS JOIN
       table2 t2 CROSS JOIN JOIN
       (SELECT SUM(t1.result) FROM table1 t1 WHERE t1.email = '[email protected]') t1
   SET t3.temp = t1.result + t2.resource;

Are you sure you don't really want an insert instead?

Upvotes: 0

Related Questions