Skip to main content
Known Participant
January 24, 2009
Question

Query question

  • January 24, 2009
  • 3 replies
  • 581 views
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 ?
    This topic has been closed for replies.

    3 replies

    Inspiring
    January 25, 2009
    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?
    Inspiring
    January 25, 2009
    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)
    January 25, 2009
    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.
    Known Participant
    January 25, 2009
    I thought of the cfloop query solution on my drive home and I think it will work but have not tried yet. The only difference from your solution is that I thought it would be an update instead of insert. If I add the new column to table B, it initially will be blank or null. Am I updating with the value from Table A or am I inserting with the value from table A ?
    January 25, 2009
    Yes, sorry, I meant to say you'd UPDATE the values...since your column and rows are already in table B.

    As a general rule, whenever Dan and I post in the same thread, I'd more safely listen to what he says...he's the expert, I'm not even a CF professional...I use it in my spare time.

    That being said, use what is best for you in your particular situation. If it's a one off thing, then you obviously don't have to worry too much about performance.

    Good luck,
    Mikey.