|
View previous topic :: View next topic
|
| Author |
Message |
Daphne
New User
Joined: 28 Aug 2006
Posts: 27
Location: NY, US
|
|
|
|
Hi all,
Is there anything wrong with this query? If yes, please explain.
UPDATE <table-name>
SET Field1 = :WS-FIELD-VALUE
WHERE Field1 > :WS-FIELD-VALUE
AND Field2 = 's'
Here Field1 and Field2 make up the primary key.
Thanks,
Daphne |
|
| Back to top |
|
 |
MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
|
|
| I think, If field you are updating is part of Primary Key or Partioning key, you would have to DELETE and INSERT. And if there are related child tables and DELETE CASCADE is not enforced, then DELETE should start with child table first. |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Hi Daphne,
What I see in the SQL you gave, and I don't think a syntax error will be given, is that a primary key is, by definition, is also unique. Your SQL will, if field1 and field2 are the primary key end in an -803 SQLCODE
Dave |
|
| Back to top |
|
|
Daphne
New User
Joined: 28 Aug 2006
Posts: 27
Location: NY, US
|
|
|
|
Hi Mfrasheed/Dave,
Thanks for your replies. Dave as you said it ended up with -803.
Guess would have DELETE and then INSERT as per Mfrasheed's reply.
Thanks,
Daphne |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Hi Daphne,
I don't think delete/insert will work either. If I understand your SQL, you are going to create, whether by update or delete/insert, multiple rows where field1 = :WS-FIELD_VALUE and field2 = 'S'. If, as you say, these are the Primary Key, the second time you try to create a row with this key will get you a duplicate (-803 SQLCODE) |
|
| Back to top |
|
|
Daphne
New User
Joined: 28 Aug 2006
Posts: 27
Location: NY, US
|
|
|
|
Hi Dave,
Yes, If I use DELETE/INSERT together it would first try to insert and would end up with -803.
I should be selecting with a cursor and save the values in working storage, delete the row and then insert the row with changed values.
I guess that should work. Is there any other way to do this?
Please gimme your comments.
Thanks,
Daphne |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
| Daphne wrote: |
UPDATE <table-name>
SET Field1 = :WS-FIELD-VALUE
WHERE Field1 > :WS-FIELD-VALUE
AND Field2 = 's'
|
Daphne,
I think you missunderstand what I'm getting at, or, I missunderstand what you are trying to do.
With the above SQL, if there is more than 1 row with Field1 > :WS-FIELD_VALUE you are going to try and create more than one row where FIELD1 = :WS-FIELD-VALUE and FIEDL2 = 's'. It doesn't matter what the old FIELD1 value was, You will get the -803 SQLCODE when you try to create the second row with FIELD1 = :WS-FIELD-VALE and FIELD2 = 's'
Lets take an example
| Code: |
FIELD1 FIELD2 FIELD3
10 S 45
25 D 16
30 S 05
34 S 09
|
Assuming WS-FIELD-VALUE = 27 the above SQL would want to Update rows 3 and 4. Row 3 to "27 S 05" and row 4 to "27 S 09". The problem comes in when the second update or insert occurs with the same FIELD1|FIELD2 values. Since these are unique keys, DB2 will give you a -803 SQLCODE when you try to update or insert the second time.
Dave |
|
| Back to top |
|
|
Daphne
New User
Joined: 28 Aug 2006
Posts: 27
Location: NY, US
|
|
|
|
Dave,
Phew!!...At last you made me understand. You are perfectly right.
This is a logical issue.....I will have to dig into why this is being asked.
Thanks for your help.
Daphne |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Any time Daphne,
Hope to chat with you soon again  |
|
| Back to top |
|
|
|
|
