Arpit Porwal Arpit Porwal - 11 months ago 38
MySQL Question

MySQL : Comparing two tables and modifying data into one

I have two tables into my database :

Table 1 :


Field Type Null Key Default Extra
------- ------------ ------ ------ ------- --------
concode varchar(20) NO PRI (NULL)
rate double YES (NULL)
vendor varchar(100) YES (NULL)
diff varchar(20) YES 'NEW'

Table 2 :

Field Type Null Key Default Extra
------ ----------- ------ ------ ------- --------
id varchar(20) NO PRI (NULL)
rate double YES (NULL)

I want to compare the rates of two tables for each id and put the results under
column in

The two tables are related as
hussainalotcallplan1.concode =

Sample Data:

concode rate vendor diff
91 0.05 gbm new


id rate
91 0.04

I've to calculate the difference between two rates and insert the difference between them into

I've Tried this :
update hussainalotcallplan1 set diff = SELECT CASE WHEN (SELECT c.concode FROM hussainalotcallplan1 ) IN (SELECT FROM tempratediff) THEN (c.rate-t.rate) END AS diff FROM hussainalotcallplan1 c, tempratediff t WHERE c.concode =;

Can anyone give me a clue about that? Thanks in Advance.

Answer Source

You can use the update-join syntax:

UPDATE hussainalotcallplan1 h
JOIN   tempratediff t ON h.concode =
SET    h.diff = h.rate - t.rate