Query question

Updating, and you don't need a loop. In fact, a loop is the inefficient way to do it.

Why do you have to do this anyhow?

quote:

---

Originally posted by: New York Guy
I have a column named Delivery in table A. I need to move that column to table B.

Tables A and B are joined together by an ID number. How do I update/move the contents of Delivery from table A to B ?

---

Don't.

You already have them and you can select them whenever you want.

Unless Table A is a working table and Table B is the real table. If that's the case, the syntax varies from 1 database to another. Here is how to do it in redbrick.

update b
set thefield = a.thefield
from tableb b join tablea a using (thefield)

I'm not sure if you can "move" a column across to another table...at least, I've never done that, and I can't imagine there would be SQL keywords for it.

However, it's quite simple really. Just create a new column on table B (which looks and accepts the same type of data that you wanted to copy across).

Now you have the new column. Clearly you want to move the data over. So now you can query table A for all the data. Then, in a <cfloop query="" /> you could do another query that INSERTS the data into your new column.

Does that help?

<cfquery name="getTableA">
SELECT ID, columnName
FROM A
</cfquery>

<cfloop query="getTableA">

<cfquery name="insertIntoTableB">
<!--- Insert into table B's new column all the data from table A where the ID's are the same --->
<!--- e.g. WHERE ID = getTableA.ID --->
<!---- Obviously I can't be arsed to write the actual query, but you should get the idea --->
</cfquery>

</cfloop>

Good luck.
Mikey.