SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL update multiple rows using one query

    Hi Guys!

    I have read on some forums that its possible to update multiple rows using one query.

    I would like to do something like the following, but it doesn't work. Any suggestions welcome.

    PHP Code:
    update settings set case when array_key 'email_type' then value='SMTP' 

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You don't need the when ... then ... construction here.

    If I understand correctly what you want, you should use:

    Code mysql:
    UPDATE
       settings
    SET
       value='SMTP' 
    WHERE
       array_key'email_type'
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    You don't need the when ... then ... construction here.

    If I understand correctly what you want, you should use:

    Code mysql:
    UPDATE
       settings
    SET
       value='SMTP' 
    WHERE
       array_key'email_type'
    Sorry this is not what I meant. I seem a bit unclear in my original post, but what I want to do is the following:

    PHP Code:
    update settings set case
    when array_key='email_type' then value='SMTP'
    when array_key 'email_host' then value='server.gmail.com' 

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    update settings 
       set value = case array_key
                   when 'email_type' then 'SMTP'
                   when 'email_host' then 'server.gmail.com' end
     where array_key in ('email_type','email_host')

  5. #5
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swampBoogie View Post
    Code:
    update settings 
       set value = case array_key
                   when 'email_type' then 'SMTP'
                   when 'email_host' then 'server.gmail.com' end
     where array_key in ('email_type','email_host')
    Ok great, I am running the following query, but it doesn't update any rows:

    PHP Code:
    update settings set value 
    case 
    array_key 
    when 
    'email_type' then 'SMTP' 
    when 'email_host' then '' 
    when 'email_port' then '' 
    when 'email_username' then '' 
    when 'email_password' then '' end 
    where array_key in 
    ('email_type','email_host','email_port','email_username','email_password'
    Here is my current table dump:

    PHP Code:
    CREATE TABLE `settings` (
      `
    idsmallint(6NOT NULL AUTO_INCREMENT,
      `
    array_keyvarchar(100NOT NULL,
      `
    valuevarchar(100) DEFAULT NULL,
      `
    categoryvarchar(100NOT NULL,
      
    PRIMARY KEY (`id`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

    --
    -- 
    Dumping data for table `settings`
    --

    INSERT INTO `settingsVALUES(1'admin_session_name''jbs_admin''general');
    INSERT INTO `settingsVALUES(2'language''/includes/languages/en/default.php''general');
    INSERT INTO `settingsVALUES(3'admin_cookie_name''jbs_admin_cookie''general');
    INSERT INTO `settingsVALUES(4'max_featured_advertisers''5''');
    INSERT INTO `settingsVALUES(5'max_job_of_week''2''');
    INSERT INTO `settingsVALUES(6'email_type''SMTP''email');
    INSERT INTO `settingsVALUES(7'smtp_host'NULL'email');
    INSERT INTO `settingsVALUES(8'smtp_port'NULL'email');
    INSERT INTO `settingsVALUES(9'smtp_username'NULL'email');
    INSERT INTO `settingsVALUES(10'smtp_password'NULL'email'); 

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    email_type already has the value you want to set, and the others don't exist in your table data.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs View Post
    Ok great, I am running the following query, but it doesn't update any rows:
    because there is only one row that satisfied the WHERE clause

    it attempts to set the value for that row to SMTP, but that's the value it has already, so it doesn't bother updating it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    darn, guido you're too fast sometimes...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    darn, guido you're too fast sometimes...

    Thank you

  10. #10
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok guys, just tried this, but it still doesn't update:

    Code:
    update settings set value = 
    
    case array_key 
    
    when 'email_type' then 'SMTP' 
    
    when 'email_host' then 'test' 
    
    when 'email_port' then 'tes' 
    
    when 'email_username' then 'tes' 
    
    when 'email_password' then '' end 
    
    where array_key in ('email_type','email_host','email_port','email_username','email_password')

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I don't see any difference with your previous query? So my answer remains the same as well.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    let's hit him over the head with a 2-by-4...

    your table contains keys 'smtp_host' and 'smtp_port'

    you're trying to update the values for keys 'email_host' and 'email_port'

    it's just not gonna happen

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    let's hit him over the head with a 2-by-4...

    your table contains keys 'smtp_host' and 'smtp_port'

    you're trying to update the values for keys 'email_host' and 'email_port'

    it's just not gonna happen

    Ohhhh dear sorry guys!! I need some more coffee to keep me awake.


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
  •