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
 
Inserting a row by copying value from existing row.

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

New User


Joined: 28 Aug 2008
Posts: 2
Location: Bangalore

PostPosted: Wed Sep 24, 2008 5:47 pm    Post subject: Inserting a row by copying value from existing row.
Reply with quote

Hello,
How can I select an existing record from the table and change the primary key value, other fields unchanged and insert back as a new record or replace the existing one.
Back to top
View user's profile Send private message

anil.csk

New User


Joined: 22 Oct 2007
Posts: 16
Location: Noida

PostPosted: Wed Sep 24, 2008 6:20 pm    Post subject: Reply to: Inserting a row by copying value from existing row
Reply with quote

Use the query:

Insert into tablename (fields name) select fields names from table where primary = anything what wever you want..
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 24, 2008 10:03 pm    Post subject:
Reply with quote

Hello,

And after the insert is successful, you need to delete the original row. . .
Back to top
View user's profile Send private message
Krishna Velamur

New User


Joined: 22 Aug 2008
Posts: 22
Location: Hyderabad

PostPosted: Thu Sep 25, 2008 11:23 am    Post subject:
Reply with quote

Hi,
There are two ways of doing it.

-> Insert a new record and delete the old record, as suggested by Dick.
-> Directly update using Update query (if there is no referential integrity), but in this case you need to reorg the index. This is not adviceble.

So the first solution is the best one. Corrections are welcome.
Back to top
View user's profile Send private message
feellikeneo

New User


Joined: 19 Mar 2007
Posts: 73
Location: Chennai

PostPosted: Thu Sep 25, 2008 12:14 pm    Post subject:
Reply with quote

Hi,

I have a similar query where in i need to insert a row into TABLE1 whose one of the column value should be from TABLE2. I used the below query,

Code:
INSERT INTO QUAL.TABLE1
VALUES
(  '05'
  ,'XXXXX'
  ,(SELECT TBL2.COL1
     FROM    QUAL.TABLE2 TBL2
     WHERE  TBL2.COL2 = 'YY')
  ,CURRENT TIMESTAMP
)

But the above query failed giving a SQL code of -4700 saying
ERROR: ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE

Could you please help me to over come the above error.
Please correct my query if anything wrong.

Appreciate your help on this.
Back to top
View user's profile Send private message
Venu Rao

New User


Joined: 01 Nov 2007
Posts: 4
Location: Westborough MA

PostPosted: Fri Sep 26, 2008 2:14 am    Post subject: Inserting a row by copying value from existing row.
Reply with quote

here you go.

INSERT INTO TABLE1
SELECT
'YYYY' <---- PRIMARY KEY
,'ZZZZ' <---- PRIMARY KEY
,COL3
,COL4
,COL5
FROM TABLE1
WHERE
PMK COL1 = AAAA
AND PMK COL2 = BBBB
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 26, 2008 3:04 am    Post subject:
Reply with quote

Hello Venu Rao,

I believe there may be some correction needed for the posted sql.

If you post the correction, i'll delete this reply and the original icon_smile.gif
Back to top
View user's profile Send private message
feellikeneo

New User


Joined: 19 Mar 2007
Posts: 73
Location: Chennai

PostPosted: Fri Sep 26, 2008 8:28 am    Post subject:
Reply with quote

Dick,

We are using DB2 V7.1 here.

Venu,

Thank you very much for your query. I was able to make it. Here is what I used,

Code:
INSERT INTO QUAL.TABLE1
VALUES
(  SELECT
   '05'
  ,'XXXXX'
  ,TBL2.COL1
  ,CURRENT TIMESTAMP
    FROM    QUAL.TABLE2 TBL2
    WHERE  TBL2.COL2 = 'YY')
)


Great piece of code !!
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 26, 2008 8:47 am    Post subject: Reply to: Inserting a row by copying value from existing row
Reply with quote

Hi Neo,

Good to hear it is working - thank you for letting us know icon_smile.gif

d
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 String after '.' (period) is not copy... balaji81_k SYNCSORT 6 Wed Aug 23, 2017 9:35 pm
No new posts editting the file by inserting zeros ... pramodrsingh DFSORT/ICETOOL 9 Mon Aug 21, 2017 5:58 pm
No new posts copying data without knowing location Arun Raj DFSORT/ICETOOL 12 Thu Jul 20, 2017 1:03 pm
No new posts Copying Panvalet members to PDS LOGESHWARAN K CA Products 4 Mon Mar 06, 2017 10:45 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm

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