Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2010
    Posts
    87

    Unanswered: How Do I Copy Column from One Table to Another?

    Hello,

    I could have sworn I had this one done before but for whatever reason I can't find a how-to that works.

    I have two tables (TABLE1 & TABLE2) I need to copy of the following.

    COLUMN1 from TABLE1
    to
    COLUMN1 to TABLE2 (the column names are the same)

    How do you do this? What kind of query?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE table2
    INNER 
      JOIN table1
        ON table1.keyfld = table2.keyfld
       SET table2.column1 = table1.column1
     WHERE table2.column9 = 'foo'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2010
    Posts
    87
    Thank you!

    In the "WHERE" line, is the part that says "column9" supposed to be 9, 2 or 1?

    I tried it on the fly and it says "TABLE1 keyfld" with a text box. Is "keyfld" supposed to be a primary key field name? And what if there isn't a primary key field? I tried to make one and it says "Primary key cannot contain a null value" because some of the records are blank.

    Also, what does "foo" mean and do?
    Last edited by RBusiness; 02-25-12 at 10:35. Reason: Typos

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you asked how to copy a column from one table to another, but left out a bunch of information

    so my answer filled in the missing stuff with reasonable guesses

    regarding the key fields, you have to be able to match the rows properly, otherwise how do you know which value of column1 goes where?

    "foo" is a metasyntactic value

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2010
    Posts
    87
    Hi r937

    I guess I should explain better! I had field in a table that had multiple image names in it. Each separated by a semicolon and space "; ". I wasn't able to split them up with ease in access so I split them up into different columns (need to) and then imported them into a new table called "ALTIMAGES". I at first didn't include the SKU in the new ALTIMAGES table and now have it set as the primary key.

    So what I'm trying to do is copy 16 columns of data from table name ALTIMAGES to table name TABLE2. They share column names for the image data.

    Copying From Table "ALTIMAGES"
    Copying Column "bsc_alt_img_2"

    Copying To Table "TABLE2"
    To Column "bsc_alt_img_2"

    I set the "sku" column is the primary key in both tables.
    I'm doing this for 15 total columns bsc_alt_img_2, bsc_alt_img_3, bsc_alt_img_4 and up to 15.

    This is what I have based on your previous instruction.

    Code:
    UPDATE TABLE2
    INNER 
      JOIN ALTIMAGES
        ON ALTIMAGES.sku = TABLE2.sku
       SET TABLE2.bsc_image_alt_2 = ALTIMAGES.bsc_image_alt_2
     WHERE TABLE2.bsc_image_alt_2 = 'foo'

  6. #6
    Join Date
    Aug 2010
    Posts
    87
    I've tried several version and every time I run it it says 0 rows will be updated

    I think it's the 'foo' line. I don't know what goes there. I tried = is null.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does table2 already have a column called bsc_alt_img_2?

    or did you want to "add" the column to the new table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2010
    Posts
    87
    It already has it in there.

  9. #9
    Join Date
    Aug 2010
    Posts
    87
    Note: I read something about when a record is set to me a "MEMO" that it might not copy over so I switched them to "text" and still no result.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you want the column updated on all matching rows, then you don't need a WHERE clause

    can i see the query you're using now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2010
    Posts
    87
    On a whim, I thought about the code and thought how the = that is in the WHERE statement when combined with "is null" would be redundant so I removed the = and it worked. The complete working copy for anyone in the future is below.

    Thank you SO much for the structure, you have no idea how many hours this took. Over 12 (for a a minute thing) and I'm not kidding! Thank you!

    Here's a spin. If I wanted to make this query do the same thing for 14 more columns at the same time how would it look? If you can give me an example for the same TABLES and column names "bsc_image_alt_2" and "bsc_image_alt_3" that would complete my quest! Thank you!!!!!

    Code:
    UPDATE TABLE2 INNER JOIN ALTIMAGES ON ALTIMAGES.sku = TABLE2.sku SET TABLE2.bsc_image_alt_2 = ALTIMAGES.bsc_image_alt_2
    WHERE TABLE2.bsc_image_alt_2 is null

  12. #12
    Join Date
    Aug 2010
    Posts
    87
    "then you don't need a WHERE clause"

    I almost deleted it to test it! Go figure. It works with "is null too" I'll leave it alone because it works! LOL I don't want to break anything!

  13. #13
    Join Date
    Aug 2010
    Posts
    87
    r937

    I figured it out in Design View by copying things. The only thing I had to do was duplicate the blocks of settings in Design View and then put a [ and ] on the first set of data in the "UPDATE TO" box. I also removed the "Criteria" of "WHERE" like you said and it all works! Thank you VERY much for your help! This has been a thorn in my side for 2 days!

    The Update Query for Future Folks Looking

    Code:
    UPDATE TABLE2 INNER JOIN ALTIMAGES ON TABLE2.sku = ALTIMAGES.sku SET TABLE2.bsc_image_alt_2 = [ALTIMAGES.bsc_image_alt_2], TABLE2.bsc_image_alt_3 = [ALTIMAGES].[bsc_image_alt_3], TABLE2.bsc_image_alt_4 = [ALTIMAGES].[bsc_image_alt_4], TABLE2.bsc_image_alt_5 = [ALTIMAGES].[bsc_image_alt_5], TABLE2.bsc_image_alt_6 = [ALTIMAGES].[bsc_image_alt_6], TABLE2.bsc_image_alt_7 = [ALTIMAGES].[bsc_image_alt_7], TABLE2.bsc_image_alt_8 = [ALTIMAGES].[bsc_image_alt_8], TABLE2.bsc_image_alt_9 = [ALTIMAGES].[bsc_image_alt_9], TABLE2.bsc_image_alt_10 = [ALTIMAGES].[bsc_image_alt_10], TABLE2.bsc_image_alt_11 = [ALTIMAGES].[bsc_image_alt_11], TABLE2.bsc_image_alt_12 = [ALTIMAGES].[bsc_image_alt_12], TABLE2.bsc_image_alt_13 = [ALTIMAGES].[bsc_image_alt_13], TABLE2.bsc_image_alt_14 = [ALTIMAGES].[bsc_image_alt_14], TABLE2.bsc_image_alt_15 = [ALTIMAGES].[bsc_image_alt_15];

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE TABLE2 
    INNER 
      JOIN ALTIMAGES 
        ON TABLE2.sku = ALTIMAGES.sku 
       SET TABLE2.bsc_image_alt_2 = [ALTIMAGES.bsc_image_alt_2]
         , TABLE2.bsc_image_alt_3 = [ALTIMAGES].[bsc_image_alt_3]
         , TABLE2.bsc_image_alt_4 = [ALTIMAGES].[bsc_image_alt_4]
         , TABLE2.bsc_image_alt_5 = [ALTIMAGES].[bsc_image_alt_5]
         , TABLE2.bsc_image_alt_6 = [ALTIMAGES].[bsc_image_alt_6]
         , TABLE2.bsc_image_alt_7 = [ALTIMAGES].[bsc_image_alt_7]
         , TABLE2.bsc_image_alt_8 = [ALTIMAGES].[bsc_image_alt_8]
         , TABLE2.bsc_image_alt_9 = [ALTIMAGES].[bsc_image_alt_9]
         , TABLE2.bsc_image_alt_10 = [ALTIMAGES].[bsc_image_alt_10]
         , TABLE2.bsc_image_alt_11 = [ALTIMAGES].[bsc_image_alt_11]
         , TABLE2.bsc_image_alt_12 = [ALTIMAGES].[bsc_image_alt_12]
         , TABLE2.bsc_image_alt_13 = [ALTIMAGES].[bsc_image_alt_13]
         , TABLE2.bsc_image_alt_14 = [ALTIMAGES].[bsc_image_alt_14]
         , TABLE2.bsc_image_alt_15 = [ALTIMAGES].[bsc_image_alt_15];
    i'm pretty sure the square brackets on the first line of the SET clause are wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Aug 2010
    Posts
    87
    Thank you!

    In the example you just showed. Quick question, if the table was actually called "ONE BIG TABLE" with spaces, do you bracket the table name?

    I'm going to use this on a different table and this was all for my test tables. Forgot the "spaces".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •