maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

aria and myisam don't support


2014-06-12 1:27 GMT-03:00 Roberto Spadim <roberto@xxxxxxxxxxxxx>:

> 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 <roberto@xxxxxxxxxxxxx>:
>
> 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 <pivanof@xxxxxxxxxx>:
>>
>> 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 <roberto@xxxxxxxxxxxxx>
>>> 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 <pivanof@xxxxxxxxxx>:
>>> >
>>> >> On Wed, Jun 11, 2014 at 8:32 PM, Roberto Spadim <
>>> roberto@xxxxxxxxxxxxx>
>>> >> 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