UPDATE statement and unique constraints.

[Paul Reddin]

Hi,

We have been having many issues with unique constraints and we seem to
have boiled it down to the equivalent of the following very simple
scenario...

given a table with a unqiue constraint on col1 and populated as
follows

col1 col2
---- ----
bob 1
fred 2

We now want to update/swap both rows without violating the unique
constraint

i.e
update test1 set col1='fred' where col2=1;
update test1 set col1='bob' where col2=2;

The first statement ALWAYS violates the unique constraint
irrespective of whether we do either of the following

1. Turn autocommit off, i.e do both in the same Txn.
2. Wrap the two statements in BEGIN ATOMIC ... END

How should/could this be done ?

i.e the constraint should not be checked until end of statement or Txn
?

Thanks.

Paul.

[Serge Rielau]

Paul,

Neither will work in DB2 for Multiplatforms. Constraints are checked per
statement and not per transaction.
You must perform the swap in one single update statement.
Depending on the nature of the swap you may be able to use OLAP
functions or correlated queries refering back to the table.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Vernon]

"Paul Reddin" <pa**@abacus.co.uk> wrote in message
news:1f**************************@posting.google.c om...

Hi,

We have been having many issues with unique constraints and we seem to
have boiled it down to the equivalent of the following very simple
scenario...

given a table with a unqiue constraint on col1 and populated as
follows

col1 col2
---- ----
bob 1
fred 2

We now want to update/swap both rows without violating the unique
constraint

i.e
update test1 set col1='fred' where col2=1;
update test1 set col1='bob' where col2=2;

UPDATE test1
set col1 = CASE WHEN col2 =1 then 'fred' WHEN col2=2 then 'bob' END
WHERE col2 = 1 or col2 = 2
;
The first statement ALWAYS violates the unique constraint
irrespective of whether we do either of the following

1. Turn autocommit off, i.e do both in the same Txn.
2. Wrap the two statements in BEGIN ATOMIC ... END

How should/could this be done ?

i.e the constraint should not be checked until end of statement or Txn
?

Regards
Paul Vernon
Business Intelligence, IBM Global Services

[Paul Reddin]

Serge/Paul

Thanks for your answers, not what I wanted to hear, but thanks...

Unfortunately, rewriting in such a way is probably not an option
as the updates are formulated by Toplink (OO/ER layer).

I thought BEGIN ATOMIC...END caused the multiple statements to behave as
a single statement? or is this just an exception to that rule?

Thanks.

Paul.

"Paul Vernon" <pa*********@ukk.ibmm.comm> wrote in message news:<bq***********@gazette.almaden.ibm.com>...

"Paul Reddin" <pa**@abacus.co.uk> wrote in message
news:1f**************************@posting.google.c om...

Hi,

We have been having many issues with unique constraints and we seem to
have boiled it down to the equivalent of the following very simple
scenario...

given a table with a unqiue constraint on col1 and populated as
follows

col1 col2
---- ----
bob 1
fred 2

We now want to update/swap both rows without violating the unique
constraint

i.e
update test1 set col1='fred' where col2=1;
update test1 set col1='bob' where col2=2;

UPDATE test1
set col1 = CASE WHEN col2 =1 then 'fred' WHEN col2=2 then 'bob' END
WHERE col2 = 1 or col2 = 2
;

The first statement ALWAYS violates the unique constraint
irrespective of whether we do either of the following

1. Turn autocommit off, i.e do both in the same Txn.
2. Wrap the two statements in BEGIN ATOMIC ... END

How should/could this be done ?

i.e the constraint should not be checked until end of statement or Txn
?

Regards
Paul Vernon
Business Intelligence, IBM Global Services

[Serge Rielau]

Paul,

BEGIN ATOMIC .. END
Compiles the multiple statements into one plan.
It does not change the statement level semantics.
Semantically BEGIN ATOMIC .. END also introduces a safepoint (hence ATOMIC)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[AK]

Hi Paul,

if you happen to knoiw 2 values that are definitely not present in the
table then you could do like this:

update test1 set col1='not_a_value1' where col2=1;
update test1 set col1='not_a_value2' where col2=2;
update test1 set col1='fred' where col2=1;
update test1 set col1='bob' where col2=2;

and yes, you could wrap that in BEGIN ATOMIC block

Theoretically, there is another option: if you cannot swap col1
values, then you could swap all the other columns in these two
records, I mean

update test1 set col2=value2_fromrec2, col3=value3_fromrec2 ... where
col2=1;
update test1 set col2=value2_fromrec1, col3=value3_fromrec1 ... where
col2=2;

good luck!

[Frédéric BROUARD]

Hi,

did you try a unique query that does the work ?

Like this one (I make it on SQL Server) :

CREATE TABLE A_TEST
(col1 varchar(16) ,
col2 int not null unique)

INSERT INTO A_TEST VALUES ('bob', 1)
INSERT INTO A_TEST VALUES ('fred', 2)

UPDATE A_TEST
SET col2 = (SELECT col2
FROM A_TEST T
WHERE T.col2 + A_TEST.col2 = 3)
WHERE col2 <= 2

SELECT * FROM A_TEST

col1 col2
---------------- -----------
bob 2
fred 1

Please mail me if this do the right work on DB2. I am interested in such
cases !
A +

--
Frédéric BROUARD - expert SQL, spécialiste : SQL Server / Delphi / web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
****************** mailto:br******@club-internet.fr ******************

Paul Reddin a écrit:

Hi,

We have been having many issues with unique constraints and we seem to
have boiled it down to the equivalent of the following very simple
scenario...

given a table with a unqiue constraint on col1 and populated as
follows

col1 col2
---- ----
bob 1
fred 2

We now want to update/swap both rows without violating the unique
constraint

i.e
update test1 set col1='fred' where col2=1;
update test1 set col1='bob' where col2=2;

The first statement ALWAYS violates the unique constraint
irrespective of whether we do either of the following

1. Turn autocommit off, i.e do both in the same Txn.
2. Wrap the two statements in BEGIN ATOMIC ... END

How should/could this be done ?

i.e the constraint should not be checked until end of statement or Txn
?

Thanks.

Paul.

[Tokunaga T.]

Frédéric BROUARD <br******@club-internet.fr> wrote in message

Hi,

did you try a unique query that does the work ?

Like this one (I make it on SQL Server) :

CREATE TABLE A_TEST
(col1 varchar(16) ,
col2 int not null unique)

INSERT INTO A_TEST VALUES ('bob', 1)
INSERT INTO A_TEST VALUES ('fred', 2)

UPDATE A_TEST
SET col2 = (SELECT col2
FROM A_TEST T
WHERE T.col2 + A_TEST.col2 = 3)
WHERE col2 <= 2

SELECT * FROM A_TEST

col1 col2
---------------- -----------
bob 2
fred 1

Please mail me if this do the right work on DB2. I am interested in such
cases !

Excellent!
I tested this idea in DB2 and it worked fine.
One thing I want to point out is the following.
Original requirement is swap/update unique column col1.
So, should you use these CREATE and UPDATE SQL?
CREATE TABLE Test1
(col1 VARCHAR(10) NOT NULL UNIQUE
,col2 INTEGER NOT NULL
);

INSERT INTO Test1
VALUES ('bob', 1) , ('fred', 2);
UPDATE test1 U
SET col1 = (SELECT col1
FROM test1 T
WHERE T.col2 + U.col2 = 3);

SELECT * FROM test1;
-----------------------------------------

COL1 COL2
---------- -----------
fred 1
bob 2

2 record(s) selected.

[AK]

another option would be to delete both rows, then insert their new versions