Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating Fields from another Database

Status
Not open for further replies.

jpor

Technical User
Nov 29, 2000
212
GB
Hi All,

Little bit new to 4gl, although have done a few ISQL (Informix 7 SE SQL on AIX platform), scripts etc...

Currently I have a project where a file is presented to me in a comma seperated value format. From this I have managed to write an AIX Korn Shell Script to check for errors, remove commas and convert lowercase to uppercase characters. Once done this uploads into a specially prepared Database. Then (Because of restrictions we have in place) certain fields are put into another database table against a customer ID which has a customer store name and address. Unfortunately I have been tasked in changing the wheel. After this information is uploaded one by one by our booking in department I need to do the following:


Change the following fields in database jobsheet so that they read the customers end user name and address from database p4u_entry2 (The 1st database the comma seperated valuse is loaded into), but this needs to be done in batches. At the moment I have tried a standard update in an sql but because I am trying to get each field to match up, I get an error.

Basically this is how I am trying to update:

update jobsheet
set
jobsheet.js_del_name=p4u_entry2.pu_del_sname,
jobsheet.js_del_address1=p4u_entry2.pu_del_address1,
jobsheet.js_del_address2=p4u_entry2.pu_del_address2,
jobsheet.js_del_address3=p4u_entry2.pu_del_address3,
jobsheet.js_del_address4=p4u_entry2.pu_del_address4,
jobsheet.js_del_postcode=p4u_entry2.pu_del_postcode

Where js_cusotmer_id = "8000013"
and date(js_open_dt) =TODAY;

Can anyone advise on how I could do this in either 4GL or in a standard sql script?

Thanks in advance.


( "To become Wise, first you must ask Questions")
 
Something like this ?
UPDATE jobsheet
SET (js_del_name, js_del_address1, js_del_address2, js_del_address3, js_del_address4, js_del_postcode) = (
SELECT pu_del_sname, pu_del_address1, pu_del_address2, pu_del_address3, pu_del_address4, pu_del_postcode
FROM p4u_entry2 WHERE pu_cusotmer_id = js_cusotmer_id)
WHERE js_cusotmer_id = "8000013" AND DATE(js_open_dt) = TODAY;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Thanks PHV. Will give it a go.


( "To become Wise, first you must ask Questions")
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top