update...where not exists .... on same table -- 390 v7.1.1

[robert]

i need to update a column which is a member of the PK on this
table. there are some thousands of rows to be updated, many more
thousand already in the table.

so, i get a constraint violation doing a simple

set col1 = 'x' where col1 = 'y'

attempted (in general terms; col1 & col2 are the PK)

update table1 a
set a.col1 = 'x' where a.col1 = 'y' and not exists (
select * from table1 z where
a.col2 = z.col2 and
z.col1 = 'x')

get a SQL0104N error. the v7 SQL Reference doesn't say the
syntax is wrong, but doesn't have an example like this. can this
be done??

thanks,
robert

[Rhino]

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

i need to update a column which is a member of the PK on this
table. there are some thousands of rows to be updated, many more
thousand already in the table.

so, i get a constraint violation doing a simple

set col1 = 'x' where col1 = 'y'

attempted (in general terms; col1 & col2 are the PK)

update table1 a
set a.col1 = 'x' where a.col1 = 'y' and not exists (
select * from table1 z where
a.col2 = z.col2 and
z.col1 = 'x')

get a SQL0104N error. the v7 SQL Reference doesn't say the
syntax is wrong, but doesn't have an example like this. can this
be done??

You can't update the primary key of a table directly. This is due to the
possibility that your update will affect potentially millions of rows in
dependent tables whose foreign key is the same as the primary key you are
changing.

To change a primary key, you need to:
1. INSERT a new row that has the desired key
2. UPDATE all the foreign keys that refer to the old primary key value to
the new value
3. DELETE the old primary key

Rhino

[robert]

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<cS*********************@news20.bellglobal.co m>...

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

i need to update a column which is a member of the PK on this
table. there are some thousands of rows to be updated, many more
thousand already in the table.

so, i get a constraint violation doing a simple

set col1 = 'x' where col1 = 'y'

attempted (in general terms; col1 & col2 are the PK)

update table1 a
set a.col1 = 'x' where a.col1 = 'y' and not exists (
select * from table1 z where
a.col2 = z.col2 and
z.col1 = 'x')

get a SQL0104N error. the v7 SQL Reference doesn't say the
syntax is wrong, but doesn't have an example like this. can this
be done??

You can't update the primary key of a table directly. This is due to the
possibility that your update will affect potentially millions of rows in
dependent tables whose foreign key is the same as the primary key you are
changing.

To change a primary key, you need to:
1. INSERT a new row that has the desired key
2. UPDATE all the foreign keys that refer to the old primary key value to
the new value
3. DELETE the old primary key

Rhino

well, i was happily updating columns in the unique index (we don't
define PKs as such here in Gopher Gultch; nor FKs, but i guess that
follows), so it's not that. the EXISTS clause is meant to not
execute the UPDATE if there is already a row which has the same values
as would happen if the UPDATE did execute. from what i read in the UDB
UPDATE docs, this is permissible syntax. the 390 docs don't say one way
or the other. the APAR listing has a bunch of entries about problems
with UPDATE and subqueries, but none i saw which mention self-references.

so, it seems the answer is: don't do that.

[--CELKO--]

Notice that no correlation name is allowed in the UPDATE clause in
Standard SQL; this is to avoid some self-referencing problems that
could occur in the various improperly implemented dialects. But it
also follows the data model in Standard SQL. When you give a table
expression a correlation name, it is to act as if a materialized table
with that correlation name has been created in the database. That
table then is dropped at the end of the statement. If you allowed
correlation names in the UPDATE clause, you would be updating the
materialized table, which would then disappear and leave the base
table untouched.

Look at what you wrote in the subquery predicate; I think you meant to
see if the updated row already exists, not if the old row exists.

UPDATE Table1
SET col1 = 'x'
WHERE col1 = 'y'
AND NOT EXISTS -- to see if you are about to create a duplicate
(SELECT *
FROM Table1 AS Z
WHERE Table1.col2 = z.col2
AND z.col1 = 'y');

Don't forget to have ON UPDATE CASCADE actions on all the FOREIGN KEY
references, too!

[Knut Stolze]

Rhino wrote:

You can't update the primary key of a table directly. This is due to the
possibility that your update will affect potentially millions of rows in
dependent tables whose foreign key is the same as the primary key you are
changing.

To change a primary key, you need to:
1. INSERT a new row that has the desired key
2. UPDATE all the foreign keys that refer to the old primary key value to
the new value
3. DELETE the old primary key

Well, that's not quite correct. Of course you can update the values of a
primary key column (unless you have an MPP system and the column in
question is part of the partitioning key). What you can't do, however, is
updating a value of a primary or unique key if there are dependent records
for the value to be updated. The reason is that DB2 does not yet support
ON UPDATE CASCADE for foreign key relationships.

If your foreign key (FK) is defined as ON UPDATE NO ACTION, then the update
will succeed if the result of the update does not violate the FK. If the
FX is defined as ON UPDATE RESTRICT, then you can't update the value if
there is any dependent row.

To clarify the difference between NO ACTION and RESTRICT, the following
works in the first case and is rejected in the second:

CREATE TABLE t1 ( c1 INT NOT NULL PRIMARY KEY )
CREATE TABLE t2 ( c1 INT FOREIGN KEY REFERENCES t1 ON UPDATE NO ACTION )
INSERT INTO t1 VALUES (1), (2), (3)
INSERT INTO t2 VALUES (2), (3)

-- this works with NO ACTION and fails with RESTRICT
UPDATE t1 SET c1 = c1+1

Another option is to not use foreign keys at all and to implement a bunch of
triggers for each FK instead. Then you can handle the cascading update in
the trigger. However, I wouldn't really recommend such an approach because
it gets really messy really fast.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Serge Rielau]

Well, that's not quite correct. Of course you can update the values of a
primary key column (unless you have an MPP system and the column in
question is part of the partitioning key).

Updating the partitioning key is allowed since DB2 V7.2.
It was part of the 440.000 TpmC TPC-C result when Win2K was introduced
(good old times....)

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

[Knut Stolze]

Serge Rielau wrote:

Well, that's not quite correct. Of course you can update the values of a
primary key column (unless you have an MPP system and the column in
question is part of the partitioning key).

Updating the partitioning key is allowed since DB2 V7.2.
It was part of the 440.000 TpmC TPC-C result when Win2K was introduced
(good old times....)

That's great! I wasn't aware of that....

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[robert]

Knut Stolze <st****@de.ibm.com> wrote in message news:<c8**********@fsuj29.rz.uni-jena.de>...

Rhino wrote:

You can't update the primary key of a table directly. This is due to the
possibility that your update will affect potentially millions of rows in
dependent tables whose foreign key is the same as the primary key you are
changing.

To change a primary key, you need to:
1. INSERT a new row that has the desired key
2. UPDATE all the foreign keys that refer to the old primary key value to
the new value
3. DELETE the old primary key

Well, that's not quite correct. Of course you can update the values of a
primary key column (unless you have an MPP system and the column in
question is part of the partitioning key). What you can't do, however, is
updating a value of a primary or unique key if there are dependent records
for the value to be updated. The reason is that DB2 does not yet support
ON UPDATE CASCADE for foreign key relationships.

If your foreign key (FK) is defined as ON UPDATE NO ACTION, then the update
will succeed if the result of the update does not violate the FK. If the
FX is defined as ON UPDATE RESTRICT, then you can't update the value if
there is any dependent row.

To clarify the difference between NO ACTION and RESTRICT, the following
works in the first case and is rejected in the second:

CREATE TABLE t1 ( c1 INT NOT NULL PRIMARY KEY )
CREATE TABLE t2 ( c1 INT FOREIGN KEY REFERENCES t1 ON UPDATE NO ACTION )
INSERT INTO t1 VALUES (1), (2), (3)
INSERT INTO t2 VALUES (2), (3)

-- this works with NO ACTION and fails with RESTRICT
UPDATE t1 SET c1 = c1+1

Another option is to not use foreign keys at all and to implement a bunch of
triggers for each FK instead. Then you can handle the cascading update in
the trigger. However, I wouldn't really recommend such an approach because
it gets really messy really fast.

Google was being cranky, so i couldn't get a reply in. thanks for the
NO ACTION explanation. here in Gopher Gultch, there are not FKs or PKs
as such, only the occasional unique index. but i do have this lower
brain stem memory of making such updates on UDB; i'll need to go
back and confirm that.

thanks,
rboert