Arpit Porwal Arpit Porwal - 1 month ago 8
MySQL Question

MySQL : Comparing two tables and modifying data into one

I have two tables into my database :

Table 1 :

hussainalotcallplan1


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 :
tempratediff


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
diff
column in
hussainalotcallplan1
table.

The two tables are related as
hussainalotcallplan1.concode = tempratediff.id
.

Sample Data:
hussainalotcallplan1


concode rate vendor diff
91 0.05 gbm new


tempratediff


id rate
91 0.04


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


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


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

Answer

You can use the update-join syntax:

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