alter table test engine=innodb; begin; update test set id = case when id = '1' then '2' when id = '2' then '1' end; commit;
/* Erro SQL (1062): Duplicate entry '2' for key 'PRIMARY' */ 2014-06-12 1:28 GMT-03:00 Roberto Spadim <[email protected]>: > aria and myisam don't support > > > 2014-06-12 1:27 GMT-03:00 Roberto Spadim <[email protected]>: > > update test set id = case >> when id = '1' then '2' >> when id = '2' then '1' >> end; >> /* Erro SQL (1062): Duplicate entry '2' for key 'PRIMARY' */ >> >> >> >> 2014-06-12 1:26 GMT-03:00 Roberto Spadim <[email protected]>: >> >> that's the point, i think aria have a log before commit, and myisam >>> commit at everychange >>> must check, maybe the only possible method is transactional or aria, i >>> will try >>> >>> >>> 2014-06-12 0:57 GMT-03:00 Pavel Ivanov <[email protected]>: >>> >>> I think you need to explain better what the big task is. This >>>> particular problem is solved with this: >>>> >>>> update test set other = case >>>> when other = 'a' then 'b' >>>> when other = 'b' then 'a' >>>> end; >>>> >>>> I wonder though how would this be different from two different updates >>>> if MySQL gets interrupted after updating one row, but before updating >>>> the second one. Or if it successfully updates first row, but gets some >>>> error with the second one. MyISAM/Aria can't rollback the first row, >>>> can it? >>>> >>>> On Wed, Jun 11, 2014 at 8:48 PM, Roberto Spadim <[email protected]> >>>> wrote: >>>> > ok it with a innodb/transactional table >>>> > what about a aria/myisam/connect/federated table? >>>> > >>>> > >>>> > 2014-06-12 0:45 GMT-03:00 Pavel Ivanov <[email protected]>: >>>> > >>>> >> On Wed, Jun 11, 2014 at 8:32 PM, Roberto Spadim < >>>> [email protected]> >>>> >> wrote: >>>> >> > Guys, i'm with a "newbie" question >>>> >> > I need to swap a primary key value, for example: >>>> >> > create table test ( >>>> >> > id int not null default 0, >>>> >> > other varchar(255) not null default '', >>>> >> > primary key(id) >>>> >> > ); >>>> >> > insert into test (1,'a'); >>>> >> > insert into test (2,'b'); >>>> >> > >>>> >> > now i want that (2,'b') becomes (1,'b') and (1,'a') becomes (2,'a') >>>> >> > >>>> >> > the point is, how to do this, with only one UPDATE without >>>> duplicate >>>> >> > column >>>> >> > id value? and without delete values? >>>> >> >>>> >> How about this: >>>> >> >>>> >> begin; >>>> >> update test set other = 'b' where id = 1; >>>> >> update test set other = 'a' where id = 2; >>>> >> end; >>>> > >>>> > >>>> > >>>> > >>>> > -- >>>> > Roberto Spadim >>>> > SPAEmpresarial >>>> > Eng. Automação e Controle >>>> >>> >>> >>> >>> -- >>> Roberto Spadim >>> SPAEmpresarial >>> Eng. Automação e Controle >>> >> >> >> >> -- >> Roberto Spadim >> SPAEmpresarial >> Eng. Automação e Controle >> > > > > -- > Roberto Spadim > SPAEmpresarial > Eng. Automação e Controle > -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
