Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Is this query valid?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Daphne

New User


Joined: 28 Aug 2006
Posts: 27
Location: NY, US

PostPosted: Tue Dec 19, 2006 9:02 pm    Post subject: Is this query valid?
Reply with quote

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
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Tue Dec 19, 2006 9:56 pm    Post subject: Re: Is this query valid?
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Dec 20, 2006 2:22 am    Post subject: Re: Is this query valid?
Reply with quote

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
View user's profile Send private message
Daphne

New User


Joined: 28 Aug 2006
Posts: 27
Location: NY, US

PostPosted: Wed Dec 20, 2006 11:07 pm    Post subject: Re: Is this query valid?
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Dec 21, 2006 12:07 am    Post subject: Re: Is this query valid?
Reply with quote

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
View user's profile Send private message
Daphne

New User


Joined: 28 Aug 2006
Posts: 27
Location: NY, US

PostPosted: Thu Dec 21, 2006 1:40 am    Post subject: Re: Is this query valid?
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Dec 21, 2006 2:35 am    Post subject: Re: Is this query valid?
Reply with quote

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
View user's profile Send private message
Daphne

New User


Joined: 28 Aug 2006
Posts: 27
Location: NY, US

PostPosted: Thu Dec 21, 2006 3:02 am    Post subject: Re: Is this query valid?
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Dec 21, 2006 3:07 am    Post subject: Re: Is this query valid?
Reply with quote

Any time Daphne,

Hope to chat with you soon again icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us