CODEWITHSUNDEEP
phpmysql
Anchal Raheja
Anchal Raheja

Reputation: 29

How to subtract values of two different columns from two different tables?

Example Table Structure

Table 1 

ID      |    Name    |  Price
-----------------------------
1       |   Casio    |  30
2       |   Titan    |  40

Table 2

ID      |    Place   |  Price
-----------------------------
1       |   Cali    |  30 

2       |   Mexi    |  10

Operation to perform: Table1(Price) - Table2(Price) for ID = 1

New Table 1

ID      |    Name    |  Price
-----------------------------
1       |   Casio    |  0
2       |   Titan    |  40

ID matches in both tables

Upvotes: 1

Views: 2881

Answers (2)

Muhammed Imran Hussain
Muhammed Imran Hussain

Reputation: 2125

As you told both table will have same ID column you can use following query. 

SELECT table1.ID, table1.Name, (table1.Price-table2.Price) AS Price 
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID

If you want to update record you can use following:

UPDATE table1
    INNER JOIN table2 ON table1.ID = table2.ID
        SET table1.Price = (table1.Price-table2.Price)

Upvotes: 1

Max
Max

Reputation: 891

You should consider another database design to handle this case.

But to answer your question, you can create a view :

create view Differences2 as (
    select t1.id, t1.price - t2.price
    from t1, t2
    where t1.id = t2.id
)

Upvotes: 1

Related Questions