SQL update question

195 pts.
Tags:
AS/400
RPG
RPGILE
RPGLE
SQL
I'm new to SQl and cannot figure out how to do an update without listing every field in the record, sorry, row. The file I'm updating has about 50 fields, any of which might be changed. They are in an external ds that maps the record format, so it would be nice to say update set record = :myds but I don't see any way to do it. Any suggestions?
ASKED: December 18, 2007  11:36 PM
UPDATED: December 20, 2007  7:45 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

In order to update an entire row (sorry, record) in a table you must use a subselect that returns enough values to fill every field.

IE.
UPDATE MyFileA SET ROW = (SELECT * From MyFileB WHERE MyField = “MyValue”)

This SQL statement will update every field in the MyFileA record with the values returned from the MyFileB select statement.

…or you can explicitly nominate the field/column (s) you want to update in the row.
e.g. UPDATE MyFileA SET MyFieldA = [MyFieldA Value], MyFieldB = [MyFieldB Value]…WHERE [Conditional]
Have fun!

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • MMAGuy
    Weird, I had this exact same question... My problem is that I'm basically doing an interactive update/create file (similar to DFU). The user is able to maintain a file in the screen, so how do I go about updating the ENTIRE File without having to manually update every record? I have a datastructure (cpRec) to put in the info from the file using SQL D cpRec e ds Extname(CUSPRF) Prefix(cp) If my screen fields are the exact same fields as the file (except with a sc prefix), how can I update the file with the screen fields without doing CustNo = :scCustNo, OrderNo = :scOrderNo, Locations = :scLocation...and so on... I thought I could just use another DS for the screen fields D scRec e ds Extname(CUSPRF) Prefix(sc) and then somehow move that into the file: Exec SQL UPDATE cusprf Set Row = :scRec but when I run it I get a data decimal error on the dspf.
    85 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following