Results 1 to 10 of 10

Thread: Mysql Multiple update statements in one single update

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Mysql Multiple update statements in one single update

    I currently have the following:

    update doc_transactions set transport_allocation_value=120,transport_colour_integer=16000 where TRN_ID=1
    update doc_transactions set transport_allocation_value=130,transport_colour_integer=16001 where TRN_ID=2
    update doc_transactions set transport_allocation_value=140,transport_colour_integer=16002 where TRN_ID=3

    This works, but how can i make it more efficient to execute in a single update using Case?

    I have googled it but all the examples only show where a single field is updated, not multiple fields.

    Any help would be greatly appreciated

  2. #2
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Mysql Multiple update statements in one single update

    Code:
    UPDATE doc_transactions 
    SET transport_allocation_value=110 + (TRN_ID * 10),
    transport_colour_integer=16000 + (TRN_ID - 1) 
    WHERE TRN_ID IN (1, 2, 3)

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Multiple update statements in one single update

    sorry, my apologies the data in that example is fictitious, the values would not be consecutive
    Basically what i have works, but i was hoping to encapsulate it into a single update

  4. #4
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Mysql Multiple update statements in one single update

    https://stackoverflow.com/questions/...ltiple-columns
    https://stackoverflow.com/questions/...-in-sql-server

    Code:
    UPDATE doc_transactions SET 
    transport_allocation_value = CASE trn_id 
        WHEN 1 THEN 120 
        WHEN 2 THEN 130
        WHEN 3 THEN 140 END,
    transport_colour_integer = CASE trn_id
        WHEN 1 THEN 16000
        WHEN 2 THEN 16001
        WHEN 3 THEN 16002 END
    WHERE trn_id IN (1,2,3)

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Mysql Multiple update statements in one single update

    You can send more than one command at a time... you just need to separate them with semicolons...
    update doc_transactions set transport_allocation_value=120,transport_colour_integer=16000 where TRN_ID=1;
    update doc_transactions set transport_allocation_value=130,transport_colour_integer=16001 where TRN_ID=2;
    update doc_transactions set transport_allocation_value=140,transport_colour_integer=16002 where TRN_ID=3;

    send it as a single string and make sure the commandType is set to "text" and it should be fine.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Multiple update statements in one single update

    Thanks Guys, one question tg, would there be any difference in execution speed between the two above examples?

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Mysql Multiple update statements in one single update

    That I don't know... for something that small, if there is a difference, it's so negligable, you wouldn't notice...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Jan 2017
    Posts
    98

    Re: Mysql Multiple update statements in one single update

    Thanks tg

  9. #9
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: Mysql Multiple update statements in one single update

    A single table scan is better than 3, but like tg said - you'll probably only notice if you're dealing with millions of records.
    Lookup execution plans or EXPLAIN, to figure out how to see the details of the differences in a query.

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Mysql Multiple update statements in one single update

    The other big potential difference could be that Dex's approach will execute as a single transaction while TG's will be three separate ones. That means there's a potential difference in logic as well as performance if this is being used in a multi-user set up. that may or may not matter but it's worth considering.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

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



Click Here to Expand Forum to Full Width