Bug #34228 Bulk Update statement
Submitted: 1 Feb 2008 13:55 Modified: 3 Feb 2008 18:03
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:* OS:Any
Assigned to: CPU Architecture:Any
Tags: qc
Triage: Triaged: D5 (Feature request)

[1 Feb 2008 13:55] Olaf van der Spek
Description:
Assume strict mode is enabled and I have to update 10000 rows in a table with 100000 rows. The updates are independent of each other (so update xbt_files set leechers = 1 where fid in (1,2) doesn't work). 
Executing 10000 queries isn't efficient. There's a nice trick via insert ... on duplicate key update, but it breaks in strict mode (and strict mode is good, so I don't want to disable it).

Another way is to execute the updates via update ... case ... when ... then ... else ... end. However, this way can be 20x slower (for 10000 updates) and the syntax is worse.
So I'd like to request support for bulk updates. I think batch/bulk updates are a common operation and deserve proper and efficient support.

Possible syntax:
update2 xbt_files (fid, leechers, seeders, completed) values (3,1,2,3),(2,4,5,6) set leechers = values(leechers), seeders = values(seeders), completed = values(completed);

The syntax is just a possibility, the semantics are the most important part.

BTW, I've requested this feature before but the description was less clear and the discussion wasn't 'optimal'. ;)

How to repeat:
set sql_mode = '';

drop table if exists xbt_files;

create table xbt_files
(
	fid int not null auto_increment,
	info_hash blob not null,
	leechers int not null default 0,
	seeders int not null default 0,
	completed int not null default 0,
	flags int not null default 0,
	mtime int not null,
	ctime int not null,
	primary key (fid),
	unique key (info_hash(20))
);

insert into xbt_files (info_hash, mtime, ctime) values ('oRUImUWFyXvKItw3tVfB', unix_timestamp(), unix_timestamp()), ('7XnB2eksvUgHvavG3YPJ', unix_timestamp(), unix_timestamp()), ('9EsYv9y72FuHsL5yQYf8', unix_timestamp(), unix_timestamp());

insert into xbt_files (fid, leechers, seeders, completed) values (2,6268,4510,38453) on duplicate key update leechers = values(leechers), seeders = values(seeders), completed = values(completed);

update xbt_files set leechers = case fid when 2 then 4 else leechers end, seeders = case fid when 2 then 5 else seeders end, completed = case fid when 2 then 6 else completed end where fid in (2);

set sql_mode = 'strict_all_tables';

insert into xbt_files (fid, leechers, seeders, completed) values (3,6268,4510,38453) on duplicate key update leechers = values(leechers), seeders = values(seeders), completed = values(completed);
# ERROR 1364 (HY000): Field 'info_hash' doesn't have a default value

update xbt_files set leechers = case fid when 3 then 7 else leechers end, seeders = case fid when 3 then 8 else seeders end, completed = case fid when 3 then 9 else completed end where fid in (3);

select * from xbt_files;
[2 Feb 2008 2:04] Valeriy Kravchuk
Thank you for a feature request. Don't you think that Oracle's MERGE is better (see http://www.psoug.org/reference/merge.html, for example, for the details)?
[2 Feb 2008 10:18] Olaf van der Spek
It looks like MERGE merges two tables (<table_view_or_query>) while I'd like to 'merge' using values supplied in the query itself.
[3 Feb 2008 18:03] Valeriy Kravchuk
Anyway it looks like a reasonable feature request.