Improve update in MySQL
Several times I need to generate many insert requests, for example:
update claim set register_dt = '2014-01-29 09:48' where folio = '0021'
update claim set register_dt = '2014-04-09 11:30' where folio = '0080'
update claim set register_dt = '2014-05-06 13:00' where folio = '0123'
update claim set register_dt = '2014-05-21 12:20' where folio = '0161'
update claim set register_dt = '2014-01-03 10:40' where folio = '0001'
update claim set register_dt = '2014-01-06 09:12' where folio = '0002'
update claim set register_dt = '2014-01-06 10:40' where folio = '0003'
update claim set register_dt = '2014-01-07 13:00' where folio = '0004'
update claim set register_dt = '2014-01-08 11:50' where folio = '0005'
update claim set register_dt = '2014-01-10 12:34' where folio = '0006'
update claim set register_dt = '2014-01-13 09:45' where folio = '0007'
update claim set register_dt = '2014-01-15 09:29' where folio = '0008'
update claim set register_dt = '2014-01-15 10:05' where folio = '0009'
[... more and more]
And I get this text and execute one by one inside a small script in php or visual studio.
I want now, if done in some single unique query , to update everything in the mysql browser client. (and you do not need to execute requests every time) Tanks
I would rather use a temporary table like @GordonLinoff show. But this is technically not a single query, because you need to create and populate a temporary table.
One way you can actually do it in one query is by using a huge CASE expression:
update claim set register_dt = case folio
when '0021' then '2014-01-29 09:48'
when '0080' then '2014-04-09 11:30'
when '0123' then '2014-05-06 13:00'
when '0161' then '2014-05-21 12:20'
when '0001' then '2014-01-03 10:40'
when '0002' then '2014-01-06 09:12'
when '0003' then '2014-01-06 10:40'
when '0004' then '2014-01-07 13:00'
when '0005' then '2014-01-08 11:50'
when '0006' then '2014-01-10 12:34'
when '0007' then '2014-01-13 09:45'
when '0008' then '2014-01-15 09:29'
when '0009' then '2014-01-15 10:05'
. . .
end;
You can make the SQL statement as long as max_allowed_packet
.
source to share
The easiest way, in my opinion, would be a two step process. First, store the data in a temporary table and then use that to update.
create temporary table toupdate as
select '2014-01-29 09:48' as register_date, '0021' as folio union all
. . .
;
update claim c join
toupdate tu
on c.folio = tu.folio
set c.register_date = tu.register_date;
You can do it in one query if you like:
update claim c join
(select '2014-01-29 09:48' as register_date, '0021' as folio union all
. . .
) tu
on c.folio = tu.folio
set c.register_date = tu.register_date;
And, if the new values are in the database, you can simply reference the other original table from which they come.
source to share