RemoveChars and update database
Posted on 2006-06-23
I'm updating a form_action.cfm page to update a sql table... the numbers after RANKING_PL could be 0-400+ (see paste #1 below). I need help with the routine that will remove RANKING_PL leaving just the number = number (e.g. RANKING_PL65 = 0 would be come 65 = 0). 65 is the unique id for the sql table and the 0 is the value submitted from the form. This value could be 0-4 only. The second part which I assume would be just like the first one is YEARUSED65=0... after the manipulation would end up being 65=0. This value would be 0 or 2001-2007.
<!--- paste ##1 --->
RANKING_PL65 = 0
YEARUSED65 = 0
RANKING_PL62 = 3
YEARUSED62 = 2001
RANKING_PL63 = 4
YEARUSED63 = 2005
RANKING_PL64 = 2
YEARUSED64 = 2000
<!--- end of paste ##1 --->
I'd prefer to hit the database only once.
I'm not very good with loops but I'm assuming the values could be put into a comma delimited list and assigned a variable name and that variable name woudl be used in teh SQL query as in...
In summary, I need the above list (which is just a snippet of data <!--- paste ##1 --->) converted to an SQL query like this with minimal database hits. The list could contain any number from 1 - 400 values being submitted.
SET RANKING_PL = 3, RANKING_YEAR_USED = 2003
WHERE (fk_emp_pool_id = 35) AND (fk_skill_pool_id = 65)
fk_emp_pool_id = 35 <--- this is pulled from a session variable
fk_skill_pool_id = 65 <-- was extracted from the RANKING_PL65
RANKING_PL = 0 <-- derived from above RANKING_PL65 = 0
YEARUSED65 = 0 <-- derived from above