Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    58
    Thanks
    15
    Thanked 0 Times in 0 Posts

    copy data from one table to another

    I am trying to copy the data from one table field set into the same name of field set in another table as I think the data is over/unnecessarily normalised which needs too many joins in subsequent queries. I tried a few methods, non of which work. Here is the latest effort, and yes with hindsight the names could have been less confusing.
    [code]
    UPDATE `Products`
    SET `Products.department` = `Departments.department`
    FROM 'Departments'
    INNER JOIN 'Products' ON `Products.prodId` = `Departments.prodId`;

    Products
    CREATE TABLE `Products` (
    *`prodId` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
    *`department` int(1) NOT NULL DEFAULT '0',
    *`prodName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    *`prodPrice` decimal(4,2) NOT NULL,
    *PRIMARY KEY (`prodId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    Departments
    CREATE TABLE `Departments` (
    *`prodId` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
    *`department` int(1) NOT NULL,
    *PRIMARY KEY (`prodId`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    [code]

    cheers
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Why do you have products.department at all? Could you not simply query from departments whenever you need it?
    ` probably won't be happy surrounding an entire identifier. I cannot be 100% sure and can't test that where I am (I don't think I've ever need to backquote anything in mysql since I use the qualified names); I would expect it would want `products`.`prodid` for example.

    Edit:
    Actually, since the department pk is the prodid, you'd be better of eliminating the department table completely. This isn't a normalized table; originally I thought that the departments was a many to one relation, then noticed the PK is non-composite.
    Drop the backquotes to see if that fixes the problem. When you get the records copied, drop the departments table and update the code to never use it.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    58
    Thanks
    15
    Thanked 0 Times in 0 Posts

    fixed it the hard way

    The object of the exercise was to get rid of the Departments table by putting the data into Products.
    Couldn't get it to work after a couple of hours fiddling with 's etc. so just dropped the tables and rebuilt a single new Products with the data.
    I'll have to try and learn this maneuver on something simpler. Thanks for the help anyway.
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    OIC, so this was actually an exercise :P

    I think that you may be backwards here as well, let me check the documentation. Yeah I think you're backwards here, the documentation lists that the join is a part of the table_reference type, so that would be:
    Code:
    UPDATE Products p
    INNER JOIN Departments d ON d.prodId = p.prodId
    SET p.department = d.department
    Which actually makes more sense.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    New Coder
    Join Date
    Aug 2012
    Location
    France
    Posts
    58
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Learning by doing

    Not an exercise that anyone gave me - self inflicted - just messing about for a friend and hoping to keep my brain active in the process, Clearly it's not active enough, the answer was obvious when you pointed it out. Must say I find the concept of the various joins quite hard going.
    thanks
    A talent for speaking differently, rather than for arguing well, is the chief instrument of cultural change. Richard Rorty

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Yep, you just kinda get stuck in the way of doing it. Most of what I do are selects, so I always go down PROPERTY FROM TABLE while the update is TABLE SET PROPERTY. So easy to overlook if you don't regularly do them.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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