www.webdeveloper.com
Results 1 to 4 of 4

Thread: Multiple Row Update (Different WHERE clauses)

  1. #1
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352

    Thumbs down Multiple Row Update (Different WHERE clauses)

    Is there a way to consolidate this:

    Code:
    UPDATE `departments` (`number`,`name`,`area_of_responsibility`) VALUES (1501005,'University Ombuds',11001) WHERE `number` = 1501005 LIMIT 1;
    
    UPDATE `departments` (`number`,`name`,`area_of_responsibility`) VALUES (1501005,'University Ombuds',11001) WHERE `number` = 1501005 LIMIT 1;
    
    UPDATE `departments` (`number`,`name`,`area_of_responsibility`) VALUES (1501005,'University Ombuds',11001) WHERE `number` = 1501005 LIMIT 1;
    ...into one statements such as this:

    Code:
    UPDATE `departments` (`number`,`name`,`area_of_responsibility`) VALUES ((1501005,'University Ombuds',11001) WHERE `number` = 1501005),((1501005,'University Ombuds',11001) WHERE `number` = 1501005),((1501005,'University Ombuds',11001) WHERE `number` = 1501005);
    Of course the values wouldn't all be the same, I'm just too lazy to piece together another statement with a different WHERE clause.

    Ideas?

    Thanks for any and all help

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    provide a more concrete example, as I don't see what you're trying to consolidate there.

    also, as a comment, the use of that format for update, while it might work on mysql, doesn't work on other DBMS. you should probably stick w/ the ANSI standards.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  3. #3
    Join Date
    Jan 2005
    Location
    USA
    Posts
    352
    Code:
    UPDATE `departments` (`number`,`name`,`area_of_responsibility`) VALUES (1501005,'University Ombuds',11001) WHERE `number` = 1501005 LIMIT 1;
    and

    Code:
    UPDATE `departments` (`number`,`name`,`area_of_responsibility`) VALUES (1516000,'Inst. Research & Planning',11001) WHERE `number` = 1516000 LIMIT 1;
    consolidated into something like

    Code:
    UPDATE `departments` (`number`,`name`,`area_of_responsibility`) VALUES ((1501005,'University Ombuds',11001) WHERE `number` = 1501005), ((1516000,'Inst. Research & Planning`,11001) WHERE `number` = 1516000);

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Nope. This is the full syntax for update, from mysql's website

    http://dev.mysql.com/doc/refman/5.0/en/update.html
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles