Re: UPDATE statement and unique constraints.

Frédéric BROUARD <[EMAIL PROTECTED]> 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.