Update help needed

415 pts.
Tags:
DB2
i5
SQL
UDB for iSeries/i5
I need to perform an update as follows... Update LIBNAME.SDFILE Inner Join LIBNAME.DXFILE On SDPRDC = DXPRDC And SDSENO = DXSENO And SDORNO = DXORNO And SDLINE = DXLINE Set DXDNAM = '', DXDAD1 = '', DXDAD2 = '', DXDAD3 = '', DXDAD4 = '', DXDPCD = '', DXINVN = 0 Where SDIECD = 'SO' And SDCDAT < 20060301; I have done updates like this in MS T-SQL, but DB2/UDB SQL does not approve of the syntax. It clearly does not want anything but a single file name between the keywords Update and Set. The problem is that the inner join is an essential part of identifying the records in DXFILE that need to be updated. Any suggestions? Thanks in advance... Steve B

Answer Wiki

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

I think you could try changing your syntax to something like this:

<pre>Update LIBNAME.DXFILE
Set DXDNAM = ”,
DXDAD1 = ”,
DXDAD2 = ”,
DXDAD3 = ”,
DXDAD4 = ”,
DXDPCD = ”,
DXINVN = 0
from IBNAME.SDFILE Inner Join LIBNAME.DXFILE
On SDPRDC = DXPRDC
And SDSENO = DXSENO
And SDORNO = DXORNO
And SDLINE = DXLINE
Where SDIECD = ‘SO’ And SDCDAT < 20060301;</pre>

If that doesn’t work, this is something you could try also. It would work in some RDBMSs.

<pre>Update LIBNAME.DXFILE
Set DXDNAM = ”,
DXDAD1 = ”,
DXDAD2 = ”,
DXDAD3 = ”,
DXDAD4 = ”,
DXDPCD = ”,
DXINVN = 0
Where (DXPRDC, DXSENO, DXORNO, DXLINE) in
(select SDPRDC, SDSENO, SDORNO, SDLINE from IBNAME.SDFILE
where SDIECD = ‘SO’ And SDCDAT < 20060301);</pre>

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
  • SBatSI
    Carlos - Thanks for the response. This issue became urgent during the day yesterday, so I wrote a little RPG program to do these updates procedurally. I have recorded your response for future use - issues like this have a way of recurring. Your help is much appreciated. Regards, Steve B
    415 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