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

...

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

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 :

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 :

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

...

On Wed, Jun 11, 2014 at 8:32 PM, Roberto Spadim

...

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

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 :

aria and myisam don't support

2014-06-12 1:27 GMT-03:00 Roberto Spadim :

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 :

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 :

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

...

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

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

...

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 :

...

aria and myisam don't support

2014-06-12 1:27 GMT-03:00 Roberto Spadim :

...

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 :

...

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 :

...

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

On Thu, Jun 12, 2014 at 9:57 AM, Federico Razzoli wrote:

Duplicate data cannot be inserted in MariaDB, in no cases.

PostgreSQL has a different behaviour: transactions can optionally be "deferred", which means that integrity checks will be done on commit. This means that you can temporarly insert inconsistent data, and fix them before commit.The same option is available for foreign keys and other features that I don't remember.

Correct. Standard SQL behaviour is checking constraints at the end of a statement. The optional ability to defer a constraint check means that the check is done at the end of the transaction and not at the end of statement. (Postgers and Oracle can do that, SQL-Server does not.) No duplicate data will be inserted either, in both cases, deferred or not constraints. Any inconsistent data is only visible during the transaction, by the transaction - with the MVCC model no other transaction can see them before that one is committed. What MySQL and MariaDB does is different than standard/common SQL behaviour, it is checking the constraints after *every row update*. That is causing the problem that Roberto faces.

But we are talking about MariaDB, so... what you can do, in you example, is: UPDATE ... SET id = NULL WHERE id = 1; -- does not exist, right? UPDATE ... SET id = 1 WHERE id = 2; -- change other values UPDATE ... SET id = 1 WHERE id IS NULL;

This is fine - as long as there are not any NULL values already on the table in the id column. If there are, the 3rd UPDATE will convert all of them to 1. You can use negative values - of course if and only if all your ids are either positive or NULL: UPDATE ... SET id = -2 WHERE id = 1; UPDATE ... SET id = -1 WHERE id = 2; UPDATE ... SET id = -id WHERE id < 0; This can easily be extended for more complex updates and we only need two UPDATE statements - and I suppose better inside a transaction, for InnoDB engine: UPDATE ... SET id = CASE id WHEN 1 THEN -2 WHEN 2 THEN -1 WHEN ... END WHERE id IN (1,2) ; UPDATE ... SET id = -id WHERE id < 0; Pantelis

Or, if your case is more complex than this: 1) lock the table 2) drop the pk 3) update 4) unlock

Ciao Federico

_______________________________________________ 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

Roberto, Aria cannot lock rows. With SELECT FOR UPDATE you would get no errors (because MariaDB and MySQL, for some reason, don't like errors) but you would have no lock. Lock the whole table, or switch to InnoDB. The standard isolation level guarantees the protection you need. Federico -------------------------------------------- El jue, 12/6/14, Roberto Spadim escribió: Asunto: Re: [Maria-discuss] newbie question Para: "Pantelis Theodosiou" CC: "Maria Discuss" Fecha: jueves, 12 de junio, 2014 17:36 hi guys! thaks for explanations and ideas, well the first problem is ok, change to -1 and -2 and after 2 and 1, that was what i'm considering at the first time i see primary key duplicated, and since mysql don't suport defered insert/update/delete, ok let's workaround :)  now the full problem....think about a list with 'locked' and 'unlocked' positions, and important fields that make that entry information unique... every position (id column) must be >0, all positions <=0 i'm considering as 'inserted but not completed transition' |id|locked|other important data||1|false|blabla||2|false|blabla2||3|true|blabla3||4|false|blabla4||5|false|blabla5||6|true|blabla6| my problem now is: change it 5 to position 2, and reposition the list, position 2 will become position 4, and position 4 will become position 5, position 3 will not change since it's locked='true' i'm considering something like, change position 5 to -5, change position 4 to 5, change position 2 to 4, change -5 to 2, but how to do this only with sql language? and the second problem is: change position 2 to position 5, position 4 will become position 2, position 5 will become position 4 any other idea about how to do this? i think that's the easiest way, considering that i will need a lock at positions 2,3,4,5 before start updates, right? maybe i could do this with select * from test where id in (2,3,4,5) for update, or lock table ideas are wellcome 2014-06-12 8:32 GMT-03:00 Pantelis Theodosiou : On Thu, Jun 12, 2014 at 9:57 AM, Federico Razzoli wrote: Duplicate data cannot be inserted in MariaDB, in no cases. PostgreSQL has a different behaviour: transactions can optionally be "deferred", which means that integrity checks will be done on commit. This means that you can temporarly insert inconsistent data, and fix them before commit.The same option is available for foreign keys and other features that I don't remember. Correct. Standard SQL behaviour is checking constraints at the end of a statement. The optional ability to defer a constraint check means that the check is done at the end of the transaction and not at the end of statement. (Postgers and Oracle can do that, SQL-Server does not.)  No duplicate data will be inserted either, in both cases, deferred or not constraints. Any inconsistent data is only visible during the transaction, by the transaction - with the MVCC model no other transaction can see them before that one is committed. What MySQL and MariaDB does is different than standard/common SQL behaviour, it is checking the constraints after every row update. That is causing the problem that Roberto faces.   But we are talking about MariaDB, so... what you can do, in you example, is: UPDATE ... SET id = NULL WHERE id = 1; -- does not exist, right? UPDATE ... SET id = 1 WHERE id = 2; -- change other values UPDATE ... SET id = 1 WHERE id IS NULL; This is fine - as long as there are not any NULL values already on the table in the id column. If there are, the 3rd UPDATE will convert all of them to 1. You can use negative values - of course if and only if all your ids are either positive or NULL: UPDATE ... SET id = -2 WHERE id = 1; UPDATE ... SET id = -1 WHERE id = 2; UPDATE ... SET id = -id WHERE id < 0; This can easily be extended for more complex updates and we only need two UPDATE statements - and I suppose better inside a transaction, for InnoDB engine: UPDATE ... SET id = CASE id             WHEN 1 THEN -2             WHEN 2 THEN -1             WHEN ...          END WHERE id IN (1,2) ; UPDATE ... SET id = -id WHERE id < 0; Pantelis   Or, if your case is more complex than this: 1) lock the table 2) drop the pk 3) update 4) unlock Ciao Federico _______________________________________________ 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 _______________________________________________ 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 -- Roberto Spadim SPAEmpresarialEng. Automação e Controle -----Adjunto en línea a continuación----- _______________________________________________ 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