CODEWITHSUNDEEP
mysqlsqldatabasephpmyadminmariadb
domain90
domain90

Reputation: 109

MySQL: calculate and update a row with other rows value?

Table1

ID    X      Y
1     0.5    0.5    
2     ?      ?
3     5      5

Hello All,

Basically I have a table just like the one above. Im trying to UPDATE the value of X and Y of ID = 2 with the result of a division of the values of other colums.

I tried running this query without much success,

"UPDATE Table1 SET X = X IN (WHERE ID = 1) / Y IN (WHERE ID = 3) WHERE ID = 2"

The result that I want would be 

ID       X          Y
1        0.5        0.6    
2        (0.5/6)    (0.6/5)
3        5          6

Upvotes: 1

Views: 269

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

In many databases, you can use a JOIN. In MySQL syntax, this looks like:

UPDATE Table1 t1 JOIN
       Table1 t1_1
       ON t1_1.ID = 1 JOIN
       Table1 t1_3
       ON t1_3.ID = 3
    SET t1.X = t1_1.X / t1_3.X,
        t1.Y = t1_3.X / t1_3.Y
    WHERE t1.ID = 2;

Similar logic can be accomplished with using subqueries, but the code is a bit messier.

Upvotes: 1

Related Questions