maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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 <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
>



-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle