Updating multiple tables with JOINs

    Date: 08/31/07 (MySQL Communtiy)    Keywords: php, mysql, sql

    I have tables used for storing product information. When product information is updated, I need to archive the current data  before updating. I could extract the data with PHP and insert in the Archived Stuff tables, but I'm trying to find a way to do this with Insert/Update queries directly (without Selecting everything with PHP).

    Btw, MySQL table export (in plaintext) is under the LJ-cut section below, for your convenience.



    A user is updating a record - before touching the current data I want to back it up into the Archive tables:

    INSERT INTO archive_stuff_general (stuff_id) VALUES ($stuff_id);

    SELECT LAST_INSERT() as $archive_id

    INSERT INTO archive_stuff_colors (archive_id) VALUES ($archive_id);

    INSERT INTO archive_stuff_textures (archive_id) VALUES ($archive_id);


    Now I want to use an UPDATE statement to copy the current data to the archive tables.

    UPDATE
    archive_stuff_general t1
    LEFT JOIN archive_stuff_colors t2 ON t2.archive_id = t1.archive_id
    LEFT JOIN archive_stuff_textures t3 ON t3.archive_id = t1.archive_id
    LEFT JOIN stuff_general t4 ON t4.stuff_id=t1.stuff_id
    LEFT JOIN stuff_colors t5 ON t5.stuff_id=t4.stuff_id
    LEFT JOIN stuff_textures t6 ON t6.stuff_id=t4.stuff_id
    SET
    t1.user_entered = t4.user_entered,
    t2.color = t5.color
    t3.texture = t6.texture
    WHERE
    t1.archive_id = $archive_id

    If you haven't noticed already, here is where I am stuck.

    If there are multiple archived records with the same stuff_id, that update statement updates -all- of the records, not just the

    one with $archive_id. This is because of the third LEFT JOIN, where I join the archive to the stuff tables.

    Is there a way to do what I'm wanting to do? Or should I just do it the old fashioned way and SELECT via php, loop through

    results and insert into the archive table?


    CREATE TABLE `stuff_general` (
    `stuff_id` int(3) NOT NULL auto_increment,
    `user_entered` varchar(10) NOT NULL,
    PRIMARY KEY (`stuff_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

    CREATE TABLE `stuff_colors` (
    `stuff_id` int(3) NOT NULL,
    `color` varchar(20) NOT NULL,
    KEY `stuff_id` (`stuff_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    CREATE TABLE `stuff_textures` (
    `stuff_id` int(3) NOT NULL,
    `texture` varchar(10) NOT NULL,
    KEY `stuff_id` (`stuff_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    CREATE TABLE `archive_stuff_general` (
    `archive_id` int(3) NOT NULL auto_increment,
    `stuff_id` int(3) NOT NULL,
    `color` varchar(20) NOT NULL,
    PRIMARY KEY (`archive_id`),
    KEY `stuff_id` (`stuff_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    CREATE TABLE `archive_stuff_colors` (
    `archive_id` int(3) NOT NULL,
    `stuff_id` int(3) NOT NULL,
    `color` varchar(20) NOT NULL,
    KEY `stuff_id` (`stuff_id`),
    KEY `archive_id` (`archive_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    CREATE TABLE `archive_stuff_textures` (
    `archive_id` int(3) NOT NULL,
    `stuff_id` int(3) NOT NULL,
    `texture` varchar(10) NOT NULL,
    KEY `stuff_id` (`stuff_id`),
    KEY `archive_id` (`archive_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


    INSERT INTO `stuff_general` (`stuff_id`, `user_entered`) VALUES
    (1, 'jane'),
    (2, 'john');

    INSERT INTO `stuff_colors` (`stuff_id`, `color`) VALUES
    (1, 'green'),
    (2, 'blue');

    INSERT INTO `stuff_textures` (`stuff_id`, `texture`) VALUES
    (1, 'smooth'),
    (2, 'sandy');

    Source: http://community.livejournal.com/mysql/117672.html

« Error 1005 || Simple DB Design Question »
Search  •  Site Map  •  Set as Homepage  •  Add to Favourites  • 
antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home