I am a total noob at this so I am hoping some of you smart people can help me out. I have just done a migration of a crm system. (salesforce to vtiger) When we did the migration there was a calculated field that would not export. So now I need to find a way to take one field (amount) and subtract another (discount) and put the value into another (discount amount).
I have two different database tables that hold the three fields. One is table named "vtiger_potential" it has the "amount" field. The other table named "vtiger_potentialscf" has the two other fields called "cf_744 " (discount) and "cf_779 " (discount amount).
What I need to do is write a sql script that will go thru and subtract cf_744 from amount and put the answer in cf_779. I believe the other piece I need is that in the system they are all grouped by the potential_no which is a field in the "vtiger_potential" table.
I really have no idea how to start this. I am so new to this. So anybody out there feel like doing a good deed today let me know and see if you can help me.
Thank You in advance you will really be helping me out!
What you need to do is an UPDATE joining all three tables as follows (before running this I would strongly recommend that you test this out in a test environment and verify that it does what you need it to do. This is to avoid cleaning up if it goes wrong).
UPDATE vtiger_potential, vtiger_potentialscf
SET vtiger_potentialscf.cf_779 = vtiger_potential.amount - vtiger_potentialscf.cf_744
WHERE vtiger_potential.potential_no = vtiger_potentialscf.potential_no;
Wow thank you sooo much! I had given you the wrong field to establish the where clause but that was easy for me to figure out and fix. I really appreciate the help. The query makes perfect sense to me when I read it but with my little knowledge just had a hard time coming up with it. Practice, Practice, Practice!
Well thank you again for the quick and accurate answer. As my grandfather always said. "You will be rewarded in heaven! Cause there aint nothin I can do for you here! LOL But if you think of something you let me know. I owe you one.