SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2005
    Location
    Michigan, USA
    Posts
    434
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Multiple Table Update in MySQL

    I have an update query to make better.

    Code MySQL:
    CREATE TABLE `tickets` (
    `ticket_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `client_update` INT UNSIGNED NOT NULL ,
    `staff_update` INT UNSIGNED NOT NULL 
    ) ENGINE = MYISAM ;
     
    CREATE TABLE `ticket_posts` (
    `post_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `ticket_id` INT UNSIGNED NOT NULL ,
    `reply_by` ENUM( 'client', 'staff' ) NOT NULL 
    ) ENGINE = MYISAM ;
     
    UPDATE ticket SET staff_update_date = 0 WHERE ticket_id NOT IN ( SELECT ticket_id FROM ticket_posts WHERE reply_by = "staff" GROUP BY ticket_id );

    Fixing a bug which was setting staff_update when it shouldn't have. So any ticket without a "staff" post should have staff_update set to zero. The current query takes a while and makes the site hang while it's running. I'm thinking a multi table UPDATE will be better but how would it be written? The negation is throwing me off.
    - Robert

  2. #2
    SitePoint Evangelist
    Join Date
    Oct 2005
    Location
    Michigan, USA
    Posts
    434
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    UPDATE tickets t LEFT JOIN ticket_posts p ON( t.ticket_id = p.ticket_id AND p.reply_by = "staff" ) SET t.staff_update = 0 WHERE t.staff_update != 0 AND p.ticket_id IS NULL;

    I think that does it. Thanks to
    http://www.mysqldiary.com/mysql-left-join/

    The t.staff_update != 0 may not give me anything but I thought it should be in there.
    - Robert


Bookmarks

Posting Permissions

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