AS/400 – iSeries SQL Update of on table with values from another joined table

0 pts.
Tags:
AS/400
DB2 Universal Database
SQL
I've use an SQL utility on the AS/400 call SEQUEL by ASC, that allows updating of values in one table from values in another joined table. The syntax is something like this:

update set((a.fld1=b.fld1)(a.fld2=b.fld2)) from file1 a join file2 b on a.key1=b.key1 where a.key1='OnlyTheOneIWant'


I no longer have access to the SEQUEL tool, and I'm trying to do the same thing with SQL/400. A similar statement in SQL/400, except with only one field being updated, is like this:

UPDATE file1 a set a.fld1 = (SELECT COALESCE(b.fld1, a.fld1) FROM file2 b WHERE a.key1 = b.key1) where a.key1='OnlyTheOneIWant'


For each value I'm setting in file 1, I need a whole SQL sub-statement for file 2. That's not so bad for just one value, but I need to update 9 columns in one table with values from another, and if this is the only way, it would be easier to write an RPG program. Does anyone know an easier way to do this with SQL/400, preferably one that resembles my first example, and doesn't require a full SQL statement for each value you set?
ASKED: October 13, 2005  1:34 PM
UPDATED: May 8, 2013  6:06 PM

Answer Wiki

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

Use:

EXEC SQL UPDATE firstTable ft
SET (firstField, secondField) = (
SELECT st.stuffForFirstField,
st.stuffForSecondField
FROM secondTable st
WHERE ft.linkage=st.linkage)
WHERE firstTableCriteria=:yourCriteriaHere;

/s/Sheldon Linker

Discuss This Question:  

 
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

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