How to Loop?

[Extras]

I have managed to merge records from three tables into one table. But I made a (what I thought was minor!)goof. I crossed my columns so that data for the Country field witn went into the Zip_Code field. So, now, I am trying to move the data from the Zip_Code field into the Country. It would have been as easy as changing the field name but half the records are correct and half are not for the Zip_Code field!

So how do I move data from one field into another for the same table?

Here is the code I am using...

<CFQUERY NAME=&quot;Info_list&quot; DATASOURCE=&quot;#DSN#&quot;>
InfoID_PK, Info_Zip, Info_Country
FROM Info_list
WHERE (InfoID_PK < 447)
</CFQUERY>

<cfloop query=&quot;Info_list&quot;>
<CFQUERY NAME=&quot;Info_Update&quot; DATASOURCE=&quot;#DSN#&quot;>
UPDATE Info_list
SET Info_Country = '#Info_Zip#'
</CFQUERY>
</cfloop>

I have checked my first query and I get the proper output..however when I run the second query with the loop, it just picks the first value and inserts it right through the Info_Country field of all the records if the table..it does not even follow the restriction of the earlier key and update only data for InfoID_PK less than 47

Any suggestions would be deeply appreciated!

 

[Extras]

wel, i figured out a crude answer for myself..this is what i did..

I made a copy of the original table and then I just ran an update query form this second table that updated values into the original first table...

CFQUERY NAME=&quot;Info_list&quot; DATASOURCE=&quot;#DSN#&quot;>
InfoID_PK2, Info_Zip, Info_Country
FROM Info_list2
WHERE (InfoID_PK2 < 447)
</CFQUERY>

<cfloop query=&quot;Info_list&quot;>
<CFQUERY NAME=&quot;Info_Update&quot; DATASOURCE=&quot;#DSN#&quot;>
UPDATE Info_list
SET Info_Country = '#Info_Zip#'
WHERE InfoID_PK2= #Info_list.InfoID_PK2#
</CFQUERY>
</cfloop>

I suspect there just might be a more elegant solution without the need of creating a dummy table to copy the data over....would welcome feeback from others...