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 into one table using certain values from other table

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

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Thu Aug 31, 2006 11:26 pm    Post subject: Update into one table using certain values from other table
Reply with quote

Dear All,

I want to . Please give me enough sample examples for this scenario.

Thanks a lot in advance for your co-operation
Back to top
View user's profile Send private message

Gautam512

Active User


Joined: 05 Oct 2005
Posts: 309
Location: Vizag / US

PostPosted: Fri Sep 01, 2006 6:17 am    Post subject: Re: Update query
Reply with quote

hi,

there are so many ways , but one way which is a bit simple is:

u can get the details from the other table first and copy them into the host variables for the insertion of data.
Back to top
View user's profile Send private message
Muthukumar.PLindia

New User


Joined: 03 Jul 2006
Posts: 51
Location: Chennai

PostPosted: Fri Sep 01, 2006 6:42 am    Post subject:
Reply with quote

If both of the tables are in same system, You can try inserting a row like this

Insert into table1 (select * (or) field1, field2 from table2)

If you want to update the existing rows from the values taken into other table, then i dint tried this type of query..... let me check it any info you
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Fri Sep 01, 2006 3:49 pm    Post subject:
Reply with quote

Dear All,

I explain in more details manner my requirement

Please find the below query

SELECT RKT_ID,RKT.GES_SALDO, XVIEW.GES_SALDO,XVIEW.CODE,
DATE(XVIEW.UMS_TS),XVIEW.BETRAG
FROM RKT RKT,
(
SELECT UMS.SYS_ID,UMS.GES_SALDO,UMS.KAP_SALDO,UMS.KST_SALDO,
UMS.ZSS_SALDO,UMS.UMS_TS, UMS.CODE, UMS.BETRAG
FROM UMS
WHERE UMS.UMS_TS IN (SELECT MAX(X.UMS_TS)
FROM UMS X
WHERE X.SYS_ID = UMS.SYS_ID)
) XVIEW
WHERE RKT.RKT_ID = XVIEW.SYS_ID
AND RKT.GES_SALDO <> XVIEW.GES_SALDO
AND DATE(XVIEW.UMS_TS) > '23.08.2006'
ORDER BY RKT_ID
WITH UR
;

Now my requirement is to

update the value XVIEW.GES_SALDO (refer above query resultant) into the UMS.GES_SALDO (UMS table) table for the above criterion.

Please let me know if more information need to be given for explanation.
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Thu Sep 07, 2006 7:51 pm    Post subject: UPDATION into one table certain values from other tables
Reply with quote

Dear All,

I explain in more details manner my requirement

Please find the below query

SELECT RKT_ID,RKT.GES_SALDO, XVIEW.GES_SALDO,XVIEW.CODE,
DATE(XVIEW.UMS_TS),XVIEW.BETRAG
FROM RKT RKT,
(
SELECT UMS.SYS_ID,UMS.GES_SALDO,UMS.KAP_SALDO,UMS.KST_SALDO,
UMS.ZSS_SALDO,UMS.UMS_TS, UMS.CODE, UMS.BETRAG
FROM UMS
WHERE UMS.UMS_TS IN (SELECT MAX(X.UMS_TS)
FROM UMS X
WHERE X.SYS_ID = UMS.SYS_ID)
) XVIEW
WHERE RKT.RKT_ID = XVIEW.SYS_ID
AND RKT.GES_SALDO <> XVIEW.GES_SALDO
AND DATE(XVIEW.UMS_TS) > '23.08.2006'
ORDER BY RKT_ID
WITH UR
;

Now my requirement is to

update the value XVIEW.GES_SALDO (refer above query resultant) into the UMS.GES_SALDO (UMS table) table when the UMS.SYS_ID = XVIEW.SYS_ID

Please let me know if more information need to be given for explanation.
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 Loading data to table gives wrong for... Raghu navaikulam DB2 16 Thu Jul 13, 2017 2:11 pm
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm


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