i think neither the first idea should work... alter table test engine=innodb; begin; update test set id = 1 where id=2;
/* Erro SQL (1062): Duplicate entry '1' for key 'PRIMARY' */ update test set id = 2 where id=1; commit; 2014-06-12 1:29 GMT-03:00 Roberto Spadim <[email protected]>: > 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 > -- 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
