CODEWITHSUNDEEP
mysqlcursor
Houssem JLASSI
Houssem JLASSI

Reputation: 49

MySQL Stored Procedure and Cursor

I'm a beginner in stackoverflow and I hope that you gives me the solution for my problem, I'll explain :

I have two table : the first table (table A) contains (phone number, duration) and the second table (table B) contains (internationnal_prefix,destination, fee per minute,and length(prefix))

So what I need to do is making this update for all the data in the two table, i'll explain the algorithm after, first of all this is the update commmand : /* We'll add a column in the table A that will contains our result, we name it "result" */

 UPDATE table Table A SET result  = CASE WHEN left (phone_number,length(prefix)) = prefix_inter 
  THEN  (duration * fee per minute)

This is what I want to do for every phone number, I have already ordered the table B by prefix to have the largest prefix on the top so that the first checked is the largest code.

This code will calculate the consommation of the VOIP, so we need to detect the destination from he phone number by verifying if the phone number contains exactly the internationnal prefix or not (for example we can have 0044123 with 0.156€ per minute and 0044 for 0.08€ per minute, this is why we must begin with the largest code in the verification)

Every phone number must be verified with all the prefix in table B So finally, I need you to give me the solution cause I don't have enough knowledge in this domain...

PS:I'm using MySQL 5.5 Thanks & Regards.

Upvotes: 0

Views: 466

Answers (1)

Joni
Joni

Reputation: 111259

So, you want to calculate the price of a phone call using these two tables? One way you can do it is by looking up the fee per minute in a subselect:

UPDATE A SET result = (select A.duration*B.fee_per_minute from B where
    locate(B.prefix, A.phone_number) = 1 order by length(prefix) desc limit 1);

If B is already ordered by the prefix length you can drop the "order by".

Upvotes: 1

Related Questions