Note, that I didn't say to change id, I said to change 'other'. On Wed, Jun 11, 2014 at 9:30 PM, Roberto Spadim <robe...@spadim.com.br> wrote: > 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 <robe...@spadim.com.br>: > >> 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 <robe...@spadim.com.br>: >> >>> aria and myisam don't support >>> >>> >>> 2014-06-12 1:27 GMT-03:00 Roberto Spadim <robe...@spadim.com.br>: >>> >>>> 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 <robe...@spadim.com.br>: >>>> >>>>> 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 <piva...@google.com>: >>>>> >>>>>> 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 >>>>>> <robe...@spadim.com.br> 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 <piva...@google.com>: >>>>>> > >>>>>> >> On Wed, Jun 11, 2014 at 8:32 PM, Roberto Spadim >>>>>> >> <robe...@spadim.com.br> >>>>>> >> 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 : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
