maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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



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