Results 1 to 14 of 14

Thread: Help!!

  1. #1

    Exclamation Unanswered: Help!!

    I have two tables which are exactly alike. What I want to do is copy an entire column(DESCRIPTION)from one table(ebooks2) and put that information into the other table's(ebooks) description column where the Book # (BOOK) matches up. Is there a way to go about doing this?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Use the "insert into select" statement.

  3. #3
    INSERT INTO ebooks
    (select DESCRIPTION from ebooks2 where BOOK.ebooks2 = BOOK.ebooks);

    Would this be the correct code?

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    The table you want to copy the description field into - does it already have the book # field filled in. If that is the case then use the update statement. If no data exists in the destination table then use insert into statement. So for an update you would do the following:

    update test2
    set test2.testy = (select test1.testy from test1 where test1.testyid = test2.testyid)

    I believe this is what you are attempting to accomplish - update a second table that already has a key field populated and add additional information from the initial table to this table.

    FYI - your insert statement needs to have the table first and then the field name so ebooks2.book not book.ebooks. Look at the following web site for future guidance in this area:

    mysql

    Good luck

  5. #5
    I still get errors when trying to execute that statement. Just in case i didn't give enough information to start with here is the situation. I have two tables. Both have the exact same fields as far as the key numbers and such. There is information in both tables some of which are different, such as description and things that haven't been updated in the one table. All the descriptions from the updated table got deleted about a month ago so some of them have the value N/A in them while others that have been recently added have an actual description. I want to take the descriptions from the old table where the book id matches the new table and put that old description in the new table's description spot.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    update updatedtable
    set updatedtable.description =
    (select othertable.description
    from othertable
    where othertable.bookid = updatedtable.bookid)

    If you still have errors, please post errors and your sql statement.

    Good luck.

  7. #7
    Join Date
    May 2002
    Posts
    4
    Originally posted by rnealejr
    update updatedtable
    set updatedtable.description =
    (select othertable.description
    from othertable
    where othertable.bookid = updatedtable.bookid)
    If I tried this, I would get a "you have an error in your SQL syntax near 'select.....at line 1'". It hates having that open paren after the equals sign, absolutely hates and refuses to deal with it. So what do you do when this fails miserably? I'm trying to accomplish the same thing and having the worst luck.

    Thanks,

    Queue

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Please post your query and table structure involved. Also, include your version of mysql.

  9. #9
    Join Date
    May 2002
    Posts
    4
    The query is:

    UPDATE Chargeables
    SET Chargeables.InvoiceNo =
    (SELECT temp_invoice.invoice
    FROM temp_invoice
    WHERE temp_invoice.transid = Chargeables.TransactionID);

    MySQL version is 3.23.36 on RedHat Linux.


    Thanks!

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Try the following:

    replace into chargeables (col1, col2, col3, col4, invoiceno ...)
    select col1,col2,col3.col4, temp_invoice.invoice
    from temp_invoice, chargeables
    where temp_invoice.transid = charegables.transactionid

    FYI - More than 3 years ago mysql was going to include the ability to use subqueries in update statements - actually starting with your version. Now it has been put off until 4.?

    Let me know if you have any difficulties.

    Good luck.

  11. #11
    Join Date
    May 2002
    Posts
    4
    I get a '1066:Not unique table/alias' error. I don't believe you can do a replace on the columns you're selecting; it seems to confuse the system.

    What now?

    Queue

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    Try it without including the chargeables table in the subquery:

    replace into chargeables (col1, col2, col3, col4, invoiceno ...)
    select col1,col2,col3.col4, temp_invoice.invoice
    from temp_invoice
    where temp_invoice.transid = charegables.transactionid

  13. #13
    Join Date
    May 2002
    Posts
    4
    What if temp_invoice has none of the other fields in chargeables?

    I tried the following:

    REPLACE into Chargeables (InvoiceNo)
    SELECT temp_invoice.invoice
    FROM temp_invoice
    WHERE temp_invoice.transid = Chargeables.TransactionID;

    and received an 'ERROR 1109: Unknown table 'Chargeables' in where clause'.

    I'm about to resort to kicking the machine and see if that helps.

    Queue

  14. #14
    My query is:

    UPDATE ebooks
    SET ebooks.DESCRIPTION =
    (SELECT ebooks2.DESCRIPTION
    FROM ebooks2
    WHERE ebooks2.BOOK = ebooks.BOOK);

    MySQL version is 3.23.49 on RedHat Linux.


    Thanks

Posting Permissions

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