Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Update all the col4 values to match A table

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

New User


Joined: 17 Jul 2006
Posts: 9
Location: Bagalore

PostPosted: Thu Apr 24, 2008 4:09 pm    Post subject: Update all the col4 values to match A table
Reply with quote

Hi,

I have the following requirement:

I have two tables A & B. Both A & B are having the same columns 'col1', 'col2' and 'col3'. ('col1', 'col2' are part of primary key).
There are some rows in B table where col3 is not matching with A table.
So i need to write an SQL to update all the col4 values to match A table.

I tried the following query:
UPDATE B
SET COL3 = (SELECT COL3
FROM A WHERE A.COL1 = B.COL1 AND A.COL2 = B.COL2
AND A.COL3 <> B.COL3);

But it is giving me SQLCODE -4700.

Please help me.

Thanks & Regards,
Chandu
Back to top
View user's profile Send private message

acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Apr 24, 2008 4:19 pm    Post subject:
Reply with quote

¿SQLCODE -4700? If I'm not wrong that's not a valid sqlcode...
Back to top
View user's profile Send private message
chandu.be

New User


Joined: 17 Jul 2006
Posts: 9
Location: Bagalore

PostPosted: Thu Apr 24, 2008 4:32 pm    Post subject: Reply to: update with select
Reply with quote

Hi Acevedo,
SQLCODE -4700 is a valid code.

SQLCODE -4700 says that 'ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE'

Thanks & Regards,
Chandu
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Apr 24, 2008 4:35 pm    Post subject:
Reply with quote

This happens if you are using DB2 version 8 ... contact ur DBA and tell them to activate the new function mode or else try binding the program
with parameter NEWFUN(YES) ...
Back to top
View user's profile Send private message
acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Apr 24, 2008 4:54 pm    Post subject:
Reply with quote

yes, here we have versin 7 and this is what you get when invoke dsntiar with -4700.

Code:
DSNT413I SQLCODE    =        -4700 INVALID SQLCODE
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Fri Apr 25, 2008 4:07 pm    Post subject: Re: update with select
Reply with quote

Hi,

chandu.be wrote:
Hi,

UPDATE B
SET COL3 = (SELECT COL3
FROM A WHERE A.COL1 = B.COL1 AND A.COL2 = B.COL2
AND A.COL3 <> B.COL3);

But it is giving me SQLCODE -4700.



I suppose you need to join tables A and B.


Code:

UPDATE B                                       
   SET COL3 = (SELECT COL3                             
                          FROM A
                                  ,B
                        WHERE A.COL1 = B.COL1 AND
                                   A.COL2 = B.COL2 AND
                                   A.COL3 <> B.COL3);

Hope this works.
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us