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 5 of 5
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts

    updates for multiple rows

    I'm trying to figure out how to build out this query to update different data for multiple rows, on multiple where conditions. Not having luck yet...


    I've gotten to something like this:

    Code:
    UPDATE table SET common_name_english = 'Caramurizinho' WHERE taxonomic_genus = 'Lacmellea' AND scientific_name = 'gracilis' AND common_name_english = ''
    SET common_name_english = ‘sorvinha’ WHERE taxonomic_genus = ‘test2’ AND scientific_name = 'gracilis' AND common_name_english = ''
    SET common_name_english = ‘test3common’ WHERE taxonomic_genus = ‘test3’ AND scientific_name = 'gracilis' AND common_name_english = ''
    etcetera for several rows to set

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,122
    Thanks
    2
    Thanked 327 Times in 319 Posts
    To manipulate existing data (i.e. update rows), you would reference them using their row id. Assuming the three rows in your example above have ids of 5, 7, and 9. The query would become -

    Code:
    UPDATE table
    	SET common_name_english = CASE id
    			WHEN 5 THEN 'Caramurizinho'
    			WHEN 7 THEN 'sorvinha'
    			WHEN 9 THEN 'test3common'
    		END
    	WHERE id IN (5,7,9)
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    419
    Thanks
    18
    Thanked 2 Times in 2 Posts
    Will this prevent overwriting a field if there's already data? I don't want to overwrite - only fill data if the field is empty.

  • #4
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,988
    Thanks
    120
    Thanked 76 Times in 76 Posts
    Code:
    UPDATE table
    	SET common_name_english = CASE
    			WHEN id = 5 and common_name_english is null THEN 'Caramurizinho'
                            WHEN id = 7 and common_name_english is null THEN 'sorvinha'
    			WHEN id = 9 and common_name_english is null THEN 'test3common'
    		END
    	WHERE id IN (5,7,9)
    is null could be = '' ....
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #5
    New Coder
    Join Date
    Oct 2013
    Location
    Bangalore, Karnataka
    Posts
    43
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi,
    "UPDATE `tablename` SET `title`='Mr' WHERE `id=1 OR `id`=3 OR `id=9."
    The above cannot be achieved in single sql statement. But you can use prepared statement. Create statement once, set values and execute query for different values.


  •  

    Posting Permissions

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