-
Apr 10th, 2018, 04:09 AM
#1
Thread Starter
Lively Member
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
-
Apr 10th, 2018, 07:46 AM
#2
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)
-
Apr 10th, 2018, 08:16 AM
#3
Thread Starter
Lively Member
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
-
Apr 10th, 2018, 08:57 AM
#4
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)
-
Apr 10th, 2018, 09:32 AM
#5
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
-
Apr 10th, 2018, 09:39 AM
#6
Thread Starter
Lively Member
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?
-
Apr 10th, 2018, 10:27 AM
#7
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
-
Apr 10th, 2018, 11:04 AM
#8
Thread Starter
Lively Member
Re: Mysql Multiple update statements in one single update
-
Apr 10th, 2018, 11:14 AM
#9
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.
-
Apr 11th, 2018, 06:29 AM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|